相关参数
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.orasos.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.orasos.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.orasos.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.orasos.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.orasos.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.orasos.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.orasos.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.orasos.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.orasos.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.orasos.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.orasos.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功能,很不推荐.
