[20120220]Adaptive Cursor Sharing 與hints.txt

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.