联系:手机/微信(+86 17813235971) QQ(107644445)
作者:惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]
相关参数
SQL> select * from v$version; BANNER --------------------------------------------------------------------------- Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production PL/SQL Release 11.2.0.3.0 - Production CORE 11.2.0.3.0 Production TNS for Linux: Version 11.2.0.3.0 - Production NLSRTL Version 11.2.0.3.0 - Production SQL> show parameter optimizer_mode; NAME TYPE VALUE ------------------------------------ ---------------------- ---------------- optimizer_mode string ALL_ROWS SQL> show parameter cursor_sharing; NAME TYPE VALUE ------------------------------------ ---------------------- ---------------- cursor_sharing string EXACT SQL> select a.ksppinm name,b.ksppstvl value,a.ksppdesc description 2 from x$ksppi a,x$ksppcv b 3 where a.inst_id = USERENV ('Instance') 4 and b.inst_id = USERENV ('Instance') 5 and a.indx = b.indx 6 and upper(a.ksppinm) LIKE upper('%¶m%') 7 order by name 8 / Enter value for param: _optim_peek_user_binds old 6: and upper(a.ksppinm) LIKE upper('%¶m%') new 6: and upper(a.ksppinm) LIKE upper('%_optim_peek_user_binds%') NAME VALUE DESCRIPTION -------------------------------- ------------------------ ---------------------------------- _optim_peek_user_binds TRUE enable peeking of user binds
创建模拟表
SQL> create table t_xifenfei(id number,name varchar2(30)); Table created. SQL> begin 2 for i in 1..100000 loop 3 insert into t_xifenfei values(i,'xifenfei'); 4 end loop; 5 commit; 6 end; 7 / PL/SQL procedure successfully completed. SQL> update t_xifenfei SET name='www.xifenfei.com' where mod(id,20000)=0; 5 row updated. SQL> commit; Commit complete. SQL> create index i_xifenfei on t_xifenfei(name); Index created.
默认收集统计信息,查看执行计划
SQL> exec DBMS_STATS.gather_table_stats(user,'T_XIFENFEI',CASCADE=>TRUE); PL/SQL procedure successfully completed. SQL> set autot trace exp SQL> select id from t_xifenfei where name='xifenfei'; Execution Plan ---------------------------------------------------------- Plan hash value: 548923532 -------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 50000 | 683K| 103 (1)| 00:00:02 | |* 1 | TABLE ACCESS FULL| T_XIFENFEI | 50000 | 683K| 103 (1)| 00:00:02 | -------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("NAME"='xifenfei') SQL> select id from t_xifenfei where name='www.xifenfei.com'; Execution Plan ---------------------------------------------------------- Plan hash value: 548923532 -------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 50000 | 683K| 103 (1)| 00:00:02 | |* 1 | TABLE ACCESS FULL| T_XIFENFEI | 50000 | 683K| 103 (1)| 00:00:02 | -------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("NAME"='www.xifenfei.com') --这里可以发现,对于这样少量的列的情况,没有选择一个合适的执行计划
准确收集统计信息
SQL> exec DBMS_STATS.gather_table_stats(user,'T_XIFENFEI',CASCADE=>TRUE, 2 method_opt => 'FOR ALL COLUMNS SIZE 254',estimate_percent => 100); PL/SQL procedure successfully completed.
再次查看执行计划
SQL> select id from t_xifenfei where name='www.xifenfei.com'; Execution Plan ---------------------------------------------------------- Plan hash value: 1926396081 ------------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1 | 14 | 2 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| T_XIFENFEI | 1 | 14 | 2 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | I_XIFENFEI | 1 | | 1 (0)| 00:00:01 | ------------------------------------------------------------------------------------------ Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("NAME"='www.xifenfei.com') Statistics ---------------------------------------------------------- 0 recursive calls 1 db block gets 320 consistent gets 0 physical reads 0 redo size 418 bytes sent via SQL*Net to client 419 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 5 rows processed SQL> select id from t_xifenfei where name='xifenfei'; 99995 rows selected. Execution Plan ---------------------------------------------------------- Plan hash value: 548923532 -------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 99999 | 1367K| 103 (1)| 00:00:02 | |* 1 | TABLE ACCESS FULL| T_XIFENFEI | 99999 | 1367K| 103 (1)| 00:00:02 | -------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("NAME"='xifenfei') Statistics ---------------------------------------------------------- 0 recursive calls 1 db block gets 6970 consistent gets 0 physical reads 0 redo size 1455968 bytes sent via SQL*Net to client 73745 bytes received via SQL*Net from client 6668 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 99995 rows processed --通过这里可以看出在完整的收集表和index包括直方图信息后,数据库执行计划正常 --也说明一点:在数据列分布不均匀的时候,依靠数据库自动收集直方图还是不怎么拷贝.
使用AUTOTRACE测试
SQL> set autot trace exp SQL> var a varchar2(30); SQL> exec :a := 'www.xifenfei.com'; PL/SQL procedure successfully completed. SQL> select id from t_xifenfei where name=:a; Execution Plan ---------------------------------------------------------- Plan hash value: 548923532 -------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 50000 | 683K| 103 (1)| 00:00:02 | |* 1 | TABLE ACCESS FULL| T_XIFENFEI | 50000 | 683K| 103 (1)| 00:00:02 | -------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("NAME"=:A) --这里可以发现11g的Bind Variable Peeking 没有使用正确的执行计划,其实这个是AUTOTRACE本身的bug导致
收集下面sql执行计划(peeking测试需要)get_plan.sql脚本
SQL> select * from t_xifenfei where name='wwww.xifenfei.com' and id=100; no rows selected SQL> @get_plan.sql Rollback complete. Enter value for hash_value: 2708637417 select * from t_xifenfei where name='wwww.xifenfei.com' and id=100 ------------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | | | 2 (100)| | |* 1 | TABLE ACCESS BY INDEX ROWID| T_XIFENFEI | 1 | 14 | 2 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | I_XIFENFEI | 3 | | 1 (0)| 00:00:01 | ------------------------------------------------------------------------------------------ Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("ID"=100) 2 - access("NAME"='wwww.xifenfei.com') SQL> select * from t_xifenfei where name='xifenfei' and id=100; ID NAME ---------- ------------------------------------------------------------ 100 xifenfei 1 row selected. SQL> @get_plan.sql Rollback complete. Enter value for hash_value: 1355242984 select * from t_xifenfei where name='xifenfei' and id=100 -------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 103 (100)| | |* 1 | TABLE ACCESS FULL| T_XIFENFEI | 1 | 14 | 103 (1)| 00:00:02 | -------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter(("ID"=100 AND "NAME"='xifenfei')) --这里可以看到,两个执行计划都我们希望的
测试peeking功能
SQL> alter system flush shared_pool; System altered. SQL> select * from t_xifenfei where name='xifenfei' and id=100; ID NAME ---------- ------------------------------------------------------------ 100 xifenfei 1 row selected. SQL> @get_plan.sql Rollback complete. Enter value for hash_value: 2860562673 select * from t_xifenfei where name='xifenfei' and id=100 -------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 103 (100)| | |* 1 | TABLE ACCESS FULL| T_XIFENFEI | 1 | 14 | 103 (1)| 00:00:02 | -------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter(("ID"=100 AND "NAME"='xifenfei')) SQL> var b varchar2(30); SQL> exec :b := 'www.xifenfei.com'; PL/SQL procedure successfully completed. SQL> select * from t_xifenfei where name=:b and id=100; no rows selected SQL> @get_plan.sql Rollback complete. Enter value for hash_value: 4157424768 select * from t_xifenfei where name=:b and id=100 -------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 103 (100)| | |* 1 | TABLE ACCESS FULL| T_XIFENFEI | 1 | 14 | 103 (1)| 00:00:02 | -------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter(("ID"=100 AND "NAME"=:B)) --重新硬解析 SQL> alter system flush shared_pool; System altered. SQL> var b varchar2(30); SQL> exec :b := 'www.xifenfei.com'; PL/SQL procedure successfully completed. SQL> select * from t_xifenfei where name=:b and id=100; no rows selected SQL> @get_plan.sql Rollback complete. Enter value for hash_value: 4157424768 select * from t_xifenfei where name=:b and id=100 ------------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | | | 2 (100)| | |* 1 | TABLE ACCESS BY INDEX ROWID| T_XIFENFEI | 1 | 14 | 2 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | I_XIFENFEI | 6 | | 1 (0)| 00:00:01 | ------------------------------------------------------------------------------------------ Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("ID"=100) 2 - access("NAME"=:B) SQL> var b varchar2(30); SQL> exec :b := 'xifenfei'; PL/SQL procedure successfully completed. SQL> select * from t_xifenfei where name=:b and id=100; ID NAME ---------- ------------------------------------------------------------ 100 xifenfei 1 row selected. SQL> @get_plan.sql Rollback complete. Enter value for hash_value: 4157424768 select * from t_xifenfei where name=:b and id=100 ------------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | | | 2 (100)| | |* 1 | TABLE ACCESS BY INDEX ROWID| T_XIFENFEI | 1 | 14 | 2 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | I_XIFENFEI | 6 | | 1 (0)| 00:00:01 | ------------------------------------------------------------------------------------------ Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("ID"=100) 2 - access("NAME"=:B) --虽然oracle 11g宣称在在Bind Variable Peeking上增强了很多, --但是这里的实验,依然证明他存在问题,导致执行计划不正确
通过整体实验过程,证明几个问题:
1.默认的的DBMS_STATS收集统计信息不一定使得所有执行计划均正确,特别在数据很不均匀分布时.
2.AUTOTRACE不能跟踪Bind Variable Peeking
3.Bind Variable Peeking是在硬解析时候生效,虽然11g进行了改善,但是有些时候效果还是不明显,如果数据很不均匀,在发现sql语句很多不合适的时候,建议先删除该sql的执行计划,让其再次硬解析,碰碰运气,如果一直效果不好,建议不适用绑定参数形式(正确的执行计划,更多的硬解析)
4._optim_peek_user_binds参数可以关闭Bind Variable Peeking功能,很不推荐.