11G的 Adaptive Cursor Sharing

lfree

16:19:35 SQL> SELECT child_number, executions, buffer_gets, is_bind_sensitive, is_bind_aware, is_shareable FROM v$sql WHERE sql_id = '3aajxkvtwhz38' ;

CHILD_NUMBER EXECUTIONS BUFFER_GETS I I I
------------ ---------- ----------- - - -
           0          3         277 Y N Y


16:20:39 SQL> print :x

         X
----------
         0

SQL> Select /*+ gather_plan_statistics */ * from T where n=:x;
     0 ****************************************************************************************************
      0 ****************************************************************************************************

9900 rows selected.

16:21:08 SQL> SELECT * FROM table(dbms_xplan.display_cursor(NULL, NULL,'ALLSTATS LAST +PEEKED_BINDS'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  3aajxkvtwhz38, child number 0
-------------------------------------
Select /*+ gather_plan_statistics */ * from T where n=:x

Plan hash value: 2912310446

-----------------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |      1 |        |   9900 |00:00:00.02 |     269 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T     |      1 |      1 |   9900 |00:00:00.02 |     269 |
|*  2 |   INDEX RANGE SCAN          | I_T_N |      1 |      1 |   9900 |00:00:00.01 |      69 |
-----------------------------------------------------------------------------------------------

Peeked Binds (identified by position):
--------------------------------------

   1 - (NUMBER): 1

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("N"=:X)


24 rows selected.


--执行计划没变。

SQL> SELECT child_number, executions, buffer_gets, is_bind_sensitive, is_bind_aware, is_shareable FROM v$sql WHERE sql_id = '3aajxkvtwhz38' ;

CHILD_NUMBER EXECUTIONS BUFFER_GETS I I I
------------ ---------- ----------- - - -
           0          4         546 Y N Y

lfree

而我做
select /*+ gather_plan_statistics */ count(*) from T where n=:x

会出现两个执行计划：
INDEX RANGE SCAN
INDEX FAST FULL SCAN

lfree

如果我反过来，倒是可以使用acs的。
先X＝0；

16:32:54 SQL> exec :x :=0;
16:32:54 SQL> SElect /*+ gather_plan_statistics */ * from T where n=:x;
         0 ****************************************************************************************************
         0 ****************************************************************************************************

9900 rows selected.

16:32:54 SQL> SELECT * FROM table(dbms_xplan.display_cursor(NULL, NULL,'ALLSTATS LAST +PEEKED_BINDS'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  cv5dfzkj2zd5a, child number 0
-------------------------------------
SElect /*+ gather_plan_statistics */ * from T where n=:x

Plan hash value: 1601196873

------------------------------------------------------------------------------------
| Id  | Operation         | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |      1 |        |   9900 |00:00:00.01 |     206 |
|*  1 |  TABLE ACCESS FULL| T    |      1 |   9900 |   9900 |00:00:00.01 |     206 |
------------------------------------------------------------------------------------

Peeked Binds (identified by position):
--------------------------------------

   1 - (NUMBER): 0

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("N"=:X)


23 rows selected.
--为了不共享原来的，我修改“红色”的部分。

16:34:07 SQL> SELECT child_number, executions, buffer_gets, is_bind_sensitive, is_bind_aware, is_shareable FROM v$sql WHERE sql_id = 'cv5dfzkj2zd5a' ;

CHILD_NUMBER EXECUTIONS BUFFER_GETS I I I
------------ ---------- ----------- - - -
           0          1         206 Y N Y

lfree

SQL> exec :x :=4;
16:36:51 SQL> SElect /*+ gather_plan_statistics */ * from T where n=:x;

         N V
---------- ----------------------------------------------------------------------------------------------------
         4 ****************************************************************************************************

16:37:07 SQL> SELECT * FROM table(dbms_xplan.display_cursor(NULL, NULL,'ALLSTATS LAST +PEEKED_BINDS'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  cv5dfzkj2zd5a, child number 0
-------------------------------------
SElect /*+ gather_plan_statistics */ * from T where n=:x

Plan hash value: 1601196873

------------------------------------------------------------------------------------
| Id  | Operation         | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |      1 |        |      1 |00:00:00.01 |     157 |
|*  1 |  TABLE ACCESS FULL| T    |      1 |   9900 |      1 |00:00:00.01 |     157 |
------------------------------------------------------------------------------------

Peeked Binds (identified by position):
--------------------------------------

   1 - (NUMBER): 0

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("N"=:X)


23 rows selected.

SQL>  SELECT child_number, executions, buffer_gets, is_bind_sensitive, is_bind_aware, is_shareable FROM v$sql WHERE sql_id = 'cv5dfzkj2zd5a' ;

CHILD_NUMBER EXECUTIONS BUFFER_GETS I I I
------------ ---------- ----------- - - -
           0          2         363 Y N Y

lfree

16:38:11 SQL> exec :x :=5;

PL/SQL procedure successfully completed.

16:39:00 SQL> SElect /*+ gather_plan_statistics */ * from T where n=:x;

         N V
---------- ----------------------------------------------------------------------------------------------------
         5 ****************************************************************************************************

16:39:12 SQL> SELECT * FROM table(dbms_xplan.display_cursor(NULL, NULL,'ALLSTATS LAST +PEEKED_BINDS'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  cv5dfzkj2zd5a, child number 1
-------------------------------------
SElect /*+ gather_plan_statistics */ * from T where n=:x

Plan hash value: 2912310446

-----------------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |      1 |        |      1 |00:00:00.01 |       4 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T     |      1 |      1 |      1 |00:00:00.01 |       4 |
|*  2 |   INDEX RANGE SCAN          | I_T_N |      1 |      1 |      1 |00:00:00.01 |       3 |
-----------------------------------------------------------------------------------------------

Peeked Binds (identified by position):
--------------------------------------

   1 - (NUMBER): 5

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("N"=:X)


24 rows selected.

--改用索引。

16:39:27 SQL>  SELECT child_number, executions, buffer_gets, is_bind_sensitive, is_bind_aware, is_shareable FROM v$sql WHERE sql_id = 'cv5dfzkj2zd5a' ;

CHILD_NUMBER EXECUTIONS BUFFER_GETS I I I
------------ ---------- ----------- - - -
           0          2         363 Y N Y
           1          1           4 Y Y Y

--出现新的光标。

lfree

16:39:51 SQL> exec :x :=0;

PL/SQL procedure successfully completed.

16:40:29 SQL> SElect /*+ gather_plan_statistics */ * from T where n=:x;
。。。

         0 ****************************************************************************************************

9900 rows selected.

16:40:41 SQL> SELECT * FROM table(dbms_xplan.display_cursor(NULL, NULL,'ALLSTATS LAST +PEEKED_BINDS'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  cv5dfzkj2zd5a, child number 2
-------------------------------------
SElect /*+ gather_plan_statistics */ * from T where n=:x

Plan hash value: 1601196873

------------------------------------------------------------------------------------
| Id  | Operation         | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |      1 |        |   9900 |00:00:00.01 |     206 |
|*  1 |  TABLE ACCESS FULL| T    |      1 |   9900 |   9900 |00:00:00.01 |     206 |
------------------------------------------------------------------------------------

Peeked Binds (identified by position):
--------------------------------------

   1 - (NUMBER): 0

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("N"=:X)


23 rows selected.



16:41:05 SQL>  SELECT child_number, executions, buffer_gets, is_bind_sensitive, is_bind_aware, is_shareable FROM v$sql WHERE sql_id = 'cv5dfzkj2zd5a' ;

CHILD_NUMBER EXECUTIONS BUFFER_GETS I I I
------------ ---------- ----------- - - -
           0          2         363 Y N N
           1          1           4 Y Y Y
           2          1         206 Y Y Y

16:41:27 SQL>

--CHILD_NUMBER =0 的 is_shareable ＝'N'.

lfree

16:41:27 SQL> SELECT * FROM table(dbms_xplan.display_cursor('cv5dfzkj2zd5a', NULL,'ALLSTATS LAST +PEEKED_BINDS'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  cv5dfzkj2zd5a, child number 0
-------------------------------------
SElect /*+ gather_plan_statistics */ * from T where n=:x

Plan hash value: 1601196873

------------------------------------------------------------------------------------
| Id  | Operation         | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |      1 |        |      1 |00:00:00.01 |     157 |
|*  1 |  TABLE ACCESS FULL| T    |      1 |   9900 |      1 |00:00:00.01 |     157 |
------------------------------------------------------------------------------------

Peeked Binds (identified by position):
--------------------------------------

   1 - (NUMBER): 0

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("N"=:X)

SQL_ID  cv5dfzkj2zd5a, child number 1
-------------------------------------
SElect /*+ gather_plan_statistics */ * from T where n=:x

Plan hash value: 2912310446

-----------------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |      1 |        |      1 |00:00:00.01 |       4 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T     |      1 |      1 |      1 |00:00:00.01 |       4 |
|*  2 |   INDEX RANGE SCAN          | I_T_N |      1 |      1 |      1 |00:00:00.01 |       3 |
-----------------------------------------------------------------------------------------------

Peeked Binds (identified by position):
--------------------------------------

   1 - (NUMBER): 5

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("N"=:X)

SQL_ID  cv5dfzkj2zd5a, child number 2
-------------------------------------
SElect /*+ gather_plan_statistics */ * from T where n=:x

Plan hash value: 1601196873

------------------------------------------------------------------------------------
| Id  | Operation         | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |      1 |        |   9900 |00:00:00.01 |     206 |
|*  1 |  TABLE ACCESS FULL| T    |      1 |   9900 |   9900 |00:00:00.01 |     206 |
------------------------------------------------------------------------------------

Peeked Binds (identified by position):
--------------------------------------

   1 - (NUMBER): 0

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("N"=:X)


70 rows selected.

16:42:34 SQL>


--反过来可以采用acs，估计一些变化达不到它的要求。

lfree

或许你的机器的配置有一些不同，我大概知道acs的作用了。

monkeyai

lfree 发表于 2011-10-9 16:42
或许你的机器的配置有一些不同，我大概知道acs的作用了。

我这边再次验证，也和你一样。多来两次才肯变执行计划。

估计这优化器还有地方设置“敏感度”，多踹他两脚才会有反应。呵呵