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 level<=1000;
insert into t select 1001,'aaaa' from dual connect by level<=1000;
commit ;
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.