[20120327]Adaptive Cursor Sharing 的问题

[20120327]Adaptive Cursor SharingG 的问题

11G的新特性里面Adaptive Cursor Sharing采用新特性来解决PEEKED BIND的问题，但是必须要经过一次执行后，来改变执行计划。
但是如果在一些工具里面，它执行并不是提取全部的信息，而是取一部分就会遭遇执行计划不改变的问题。

如下站点：
http://jonathanlewis.wordpress.com/2012/03/21/acs/
按照讲解是一个BUG，但是没有给出例子，自己写个例子测试如下：

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

SQL> create table t1 as select rownum id,'test' name from dual connect by level

Table created.

SQL> insert into t1 select 1001 id,'book' name  from dual connect by level

1000 rows created.

SQL> commit;

Commit complete.

SQL> create index i_t1_id on t1(id);

Index created.

SQL>exec SYS.DBMS_STATS.GATHER_TABLE_STATS (user,'T1',Method_Opt => 'FOR ALL COLUMNS SIZE 254 ');

SQL> column data_type format a20

SQL> select table_name,column_name,data_type,histogram from dba_tab_cols where wner='SCOTT' and table_name='T1';

TABLE_NAME                     COLUMN_NAME                    DATA_TYPE            HISTOGRAM

------------------------------ ------------------------------ -------------------- ---------------

T1                             ID                             NUMBER               HEIGHT BALANCED

T1                             NAME                           CHAR                 FREQUENCY

SQL> alter system flush shared_pool;

2.执行sql语句,为了更好测试，我在toad执行，:a :=1 :

select /*+ testme */ * from t1 where id = :a;

        ID NAME

---------- ----

         1 test

column is_bind_sensitive format a20

column is_bind_aware     format a20

column is_shareable      format a20

SELECT sql_id, sql_text, child_number, plan_hash_value, executions, is_bind_sensitive, is_bind_aware, is_shareable

FROM v$sql

WHERE sql_text LIKE '%testme%' AND sql_text NOT LIKE '%sql_text%'

SQL_ID        SQL_TEXT                       CHILD_NUMBER PLAN_HASH_VALUE EXECUTIONS IS_BIND_SENSITIVE    IS_BIND_AWARE        IS_SHAREABLE

------------- ------------------------------ ------------ --------------- ---------- -------------------- -------------------- --------------------

9rx9cq6x20guk select /*+ testme */ * from t1            0      1111474805          1 Y                    N                    Y

               where id = :a

--知道sql_id=9rx9cq6x20guk.

SQL> select * from table(dbms_xplan.display_cursor('9rx9cq6x20guk',NULL,'allstats last peeked_binds'));

PLAN_TABLE_OUTPUT

-------------------------------------

SQL_ID  9rx9cq6x20guk, child number 0

-------------------------------------

select /*+ testme */ * from t1 where id = :a

Plan hash value: 1111474805

--------------------------------------------------------

| Id  | Operation                   | Name    | E-Rows |

--------------------------------------------------------

|   0 | SELECT STATEMENT            |         |        |

|   1 |  TABLE ACCESS BY INDEX ROWID| T1      |      1 |

|*  2 |   INDEX RANGE SCAN          | I_T1_ID |      1 |

--------------------------------------------------------

Peeked Binds (identified by position):

--------------------------------------

   1 - (NUMBER): 1

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.

3.在toad下执行相同的sql语句,但是带入的参数是1001:

SQL_ID        SQL_TEXT                       CHILD_NUMBER PLAN_HASH_VALUE EXECUTIONS IS_BIND_SENSITIVE    IS_BIND_AWARE        IS_SHAREABLE

------------- ------------------------------ ------------ --------------- ---------- -------------------- -------------------- --------------------

9rx9cq6x20guk select /*+ testme */ * from t1            0      1111474805          2 Y                    N                    Y

               where id = :a

--执行多次，参数1001，可以发现执行计划并没有产生子光标。

再次执行在toad下执行相同的sql语句,但是带入的参数是1001:

SQL_ID        SQL_TEXT                       CHILD_NUMBER PLAN_HASH_VALUE EXECUTIONS IS_BIND_SENSITIVE    IS_BIND_AWARE        IS_SHAREABLE

------------- ------------------------------ ------------ --------------- ---------- -------------------- -------------------- --------------------

9rx9cq6x20guk select /*+ testme */ * from t1            0      1111474805          4 Y                    N                    Y

               where id = :a

--可以发现并没有产生新的子光标.换句话讲Adaptive Cursor Sharing并没有起作用。

现在toad打开auto trace(具体操作是在sql编辑器里面，点击右键选择auto trace),主要目的是这样可以提取全部查询信息。
再次执行以上语句。参数1001

SQL_ID        SQL_TEXT                       CHILD_NUMBER PLAN_HASH_VALUE EXECUTIONS IS_BIND_SENSITIVE    IS_BIND_AWARE        IS_SHAREABLE

------------- ------------------------------ ------------ --------------- ---------- -------------------- -------------------- --------------------

9rx9cq6x20guk select /*+ testme */ * from t1            0      1111474805          5 Y                    N                    Y

               where id = :a

再次执行以上语句。参数1001

SQL_ID        SQL_TEXT                       CHILD_NUMBER PLAN_HASH_VALUE EXECUTIONS IS_BIND_SENSITIVE    IS_BIND_AWARE        IS_SHAREABLE

------------- ------------------------------ ------------ --------------- ---------- -------------------- -------------------- --------------------

9rx9cq6x20guk select /*+ testme */ * from t1            0      1111474805          5 Y                    N                    Y

               where id = :a

9rx9cq6x20guk select /*+ testme */ * from t1            1      3617692013          1 Y                    Y                    Y

               where id = :a

--可以发现现在出现了新的子光标。

SQL> select * from table(dbms_xplan.display_cursor('9rx9cq6x20guk',NULL,'allstats last peeked_binds'));

PLAN_TABLE_OUTPUT

-------------------------------------

SQL_ID  9rx9cq6x20guk, child number 0

-------------------------------------

select /*+ testme */ * from t1 where id = :a

Plan hash value: 1111474805

--------------------------------------------------------

| Id  | Operation                   | Name    | E-Rows |

--------------------------------------------------------

|   0 | SELECT STATEMENT            |         |        |

|   1 |  TABLE ACCESS BY INDEX ROWID| T1      |      1 |

|*  2 |   INDEX RANGE SCAN          | I_T1_ID |      1 |

--------------------------------------------------------

Peeked Binds (identified by position):

--------------------------------------

   1 - (NUMBER): 1

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

SQL_ID  9rx9cq6x20guk, child number 1

-------------------------------------

select /*+ testme */ * from t1 where id = :a

Plan hash value: 3617692013

-------------------------------------------

| Id  | Operation         | Name | E-Rows |

-------------------------------------------

|   0 | SELECT STATEMENT  |      |        |

|*  1 |  TABLE ACCESS FULL| T1   |   1012 |

-------------------------------------------

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

59 rows selected.

4.再次执行以上语句。参数1

SELECT sql_id, sql_text, child_number, plan_hash_value, executions, is_bind_sensitive, is_bind_aware, is_shareable

FROM v$sql

WHERE sql_text LIKE '%testme%' AND sql_text NOT LIKE '%sql_text%';

SQL_ID        SQL_TEXT                       CHILD_NUMBER PLAN_HASH_VALUE EXECUTIONS IS_BIND_SENSITIVE    IS_BIND_AWARE        IS_SHAREABLE

------------- ------------------------------ ------------ --------------- ---------- -------------------- -------------------- --------------------

9rx9cq6x20guk select /*+ testme */ * from t1            0      1111474805          5 Y                    N                    N

               where id = :a

9rx9cq6x20guk select /*+ testme */ * from t1            1      3617692013          1 Y                    Y                    Y

               where id = :a

9rx9cq6x20guk select /*+ testme */ * from t1            2      1111474805          1 Y                    Y                    Y

               where id = :a

--可以发现child_number=0 的子光标IS_SHAREABLE='N'.

5.如果要避免这样的问题，最好的方法是使用提示/*+ bind_aware */.