相关参数

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('%&param%')
  7  order by name
  8  /
Enter value for param: _optim_peek_user_binds
old   6:    and upper(a.ksppinm) LIKE upper('%&param%')
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功能,很不推荐.