11G的新特性里面Adaptive Cursor Sharing采用新特性来解决PEEKED BIND的问题，但是必须要经过一次执行后，来改变执行计划。能否绕过这个步骤，选择合理的执行计划呢?从11G开始也提供一个提示BIND_AWARE来绕过这个特性，直接选择合理的执行计划。
例子如下：
1.环境说明：
SQL> select * from v$version ;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
--建立测试例子:
create table t as select rownum id ,'test' name from dual connect by levelinsert into t select 1001,'aaaa' from dual connect by levelcommit ;
create index i_t_id on t(id);
exec dbms_stats.gather_table_stats(OWNNAME =>user,TABNAME =>'T',Method_Opt=>'FOR COLUMNS id SIZE 254');
SQL> column data_type format a20
SQL> SELECT table_name, column_name, data_type, histogram FROM dba_tab_cols WHERE table_name ='T' ;
TABLE_NAME COLUMN_NAME DATA_TYPE HISTOGRAM
------------------------------ ------------------------------ -------------------- ---------------
T NAME CHAR NONE
T ID NUMBER FREQUENCY
我建立的表id分布很不均匀。
cat dpc.sql
select * from table(dbms_xplan.display_cursor(NULL,NULL,'ALLSTATS LAST PEEKED_BINDS cost'));
2.开始测试：
SQL> variable a number;
SQL> exec :a := 42;
SQL> select /*+ BIND_AWARE */ * from t where id = :a ;
SQL> @dpc
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID cx60c99054skh, child number 0
-------------------------------------
select /*+ BIND_AWARE */ * from t where id = :a
Plan hash value: 4153437776
--------------------------------------------------------------------
| Id | Operation | Name | E-Rows | Cost (%CPU)|
--------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | 2 (100)|
| 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 2 (0)|
|* 2 | INDEX RANGE SCAN | I_T_ID | 1 | 1 (0)|
--------------------------------------------------------------------
Peeked Binds (identified by position):
--------------------------------------
1 - (NUMBER): 42
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID"=:A)
Note
-----
- Warning: basic plan statistics not available. These are only collected when:
* hint 'gather_plan_statistics' is used for the statement or
* parameter 'statistics_level' is set to 'ALL', at session or system level
30 rows selected.
SQL> exec :a := 1001;
SQL> select /*+ BIND_AWARE */ * from t where id = :a ;
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID cx60c99054skh, child number 1
-------------------------------------
select /*+ BIND_AWARE */ * from t where id = :a
Plan hash value: 1601196873
--------------------------------------------------------
| Id | Operation | Name | E-Rows | Cost (%CPU)|
--------------------------------------------------------
| 0 | SELECT STATEMENT | | | 4 (100)|
|* 1 | TABLE ACCESS FULL| T | 1012 | 4 (0)|
--------------------------------------------------------
Peeked Binds (identified by position):
--------------------------------------
1 - (NUMBER): 1001
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("ID"=:A)
Note
-----
- Warning: basic plan statistics not available. These are only collected when:
* hint 'gather_plan_statistics' is used for the statement or
* parameter 'statistics_level' is set to 'ALL', at session or system level
29 rows selected.
--可以发现执行计划在:a :=1001的情况下选择了全表扫描。
3.看看v$sql视图：
SQL> select sql_id,child_number,plan_hash_value,executions, is_bind_aware, is_shareable from v$sql
where sql_text like 'select /*+ BIND_AWARE */ %' and sql_id='cx60c99054skh';
SQL_ID CHILD_NUMBER PLAN_HASH_VALUE EXECUTIONS I I
------------- ------------ --------------- ---------- - -
cx60c99054skh 0 4153437776 1 Y Y
cx60c99054skh 1 1601196873 1 Y Y
--看看是什么原因产生子光标:
SQL> @ share.sql cx60c99054skh
old 15: and q.sql_id like ''&1''',
new 15: and q.sql_id like ''cx60c99054skh''',
SQL_TEXT = select /*+ BIND_AWARE */ * from t where id = :a
SQL_ID = cx60c99054skh
ADDRESS = 000000008D298DC0
CHILD_ADDRESS = 000000008D29A050
CHILD_NUMBER = 0
--------------------------------------------------
SQL_TEXT = select /*+ BIND_AWARE */ * from t where id = :a
SQL_ID = cx60c99054skh
ADDRESS = 000000008D298DC0
CHILD_ADDRESS = 000000008F702708
CHILD_NUMBER = 1
BIND_EQUIV_FAILURE = Y
--------------------------------------------------
PL/SQL procedure successfully completed.