11G Adaptive Cursor Sharing(ACS)的研究 (轉)

在網路上看到這篇文章，寫的不錯。特轉載在自己的blog裡，需要時檢視。文章中紅色字型部分，寫的不錯。對於帶繫結變數的sql語句的執行計劃，用set autotrace on 顯示的不對。使用 select * from table(dbms_xplan.display_cursor); 進行執行計劃的檢視

這個語句是我們在ACS測試過程總經常使用的sql

select child_number, executions, buffer_gets, is_bind_sensitive, is_bind_aware,IS_SHAREABLE S,plan_hash_value from v$sql where sql_id = 'gsmm31bu4zyca';

[@more@]

Adaptive Cursor Sharing（ACS）是又一個大膽而吸引人的11G新特性。
說它大膽是因為它試圖解決一個CBO最令人頭疼的問題：資料傾斜（data skew）和繫結變數窺視導致SQL PLAN太差。說它吸引人是因為想知道Oracle採用何種神秘的演算法讓Oracle變得更加智慧。

我們先用一個簡單的例子來走近ACS。
（注意：以下實驗的SQL PLAN的獲取不能信任set autotrace，他不會顯示各個child cursor的實際執行計劃，我們可以透過SELECT * FROM table (DBMS_XPLAN.DISPLAY_CURSOR(:sqlid, NULL, 'TYPICAL LAST'))來得到真實的PLAN。）
新建一個兩個欄位的表，其中id這列十分傾斜，並在id這列上建立index，並使用SKEWONLY選項分析表，使其生成histogram。
為了不讓其他因素干擾我的實驗並且讓讀者能夠重現，我設定如下引數:
optimizer_mode=CHOOSE（使用CBO）
optimizer_features_enable=11.2.0.1（使用最新的最佳化引數）
optimizer_capture_sql_plan_baselines=false（關閉SPM）
cursor_sharing=EXACT（使用真正的繫結變數）
_optim_peek_user_binds=true（一定要開啟繫結變數窺視）
_optimizer_adaptive_cursor_sharing=TRUE（以下三個引數預設開啟ACS）
_optimizer_extended_cursor_sharing=UDO
_optimizer_extended_cursor_sharing_rel=SIMPLE
SQL> desc testbyhao
Name Type
----- --------
ID NUMBER
NAME VARCHAR2(128)
SQL> select id,count(*) from testbyhao
group by id;
ID COUNT(*)
---------- ----------
1 104096
2 498
SQL> create index testidx on testbyhao(id);
Index created.

SQL> exec dbms_stats.gather_table_stats(user,'TESTBYHAO',
method_opt=>'for all columns size skewonly');
PL/SQL procedure successfully completed.
SQL> select COLUMN_NAME,HISTOGRAM from user_tab_columns
where TABLE_NAME='TESTBYHAO';
COLUMN_NAM HISTOGRAM
---------- ------------------------------
ID FREQUENCY
NAME HEIGHT BALANCED
先生成一個最簡單的執行計劃index range scan。
對於id=2來說，是相當合適的。
SQL> var v number;
SQL> exec :v :=2;
SQL> select /*comments*/ * from TESTBYHAO
2 where id = :v;
Plan hash value: 254123311
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 3 (100)| |
| 1 | TABLE ACCESS BY INDEX ROWID| TESTBYHAO | 387 | 8127 | 3 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | TESTIDX | 387 | | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID"=:V)
從v$SQL中，可以看到這個cursor的資料，其中IS_BIND_SENSITIVE=Y，表明使用繫結變數窺視來生成這次執行計劃，這次執行計劃是取決於這個繫結變數的，如果Oracle發現有其他的繫結變數出現，是可能生成其他的執行計劃的。
IS_BIND_AWARE=N，表明Oracle還沒有使用extended cursor sharing。
IS_SHAREABLE=Y，表明這個cursor可以被再次使用，即能夠共享；反之，設為N代表著這個cursor已經過時了，不會被再用了，這個cursor將會等待被age out出shared pool。

SQL> select CHILD_NUMBER,PLAN_HASH_VALUE,EXECUTIONS,
BUFFER_GETS/EXECUTIONS BG_PER_EX,
IS_BIND_SENSITIVE BS,IS_BIND_AWARE BA,IS_SHAREABLE S
from v$sql where hash_value=1659091011;
CHILD_NUMBER PLAN_HASH_VALUE EXECUTIONS BG_PER_EX BS BA S
------------ --------------- ---------- ---------- --- --- ---
0 254123311 1 221 Y N Y

更換繫結變數，使用id=1執行同樣的SQL.
SQL> exec :v := 1;
SQL> select /*comments*/ * from TESTBYHAO
2 where id = :v;

結果，使用繫結變數id=1的SQL使用了同樣的index range scan的cursor。這其實不是我們希望的，因為id=1時明顯走全表掃描cost更低。
v$SQL沒怎麼變，只是同樣的cursor執行次數為2了。
CHILD_NUMBER PLAN_HASH_VALUE EXECUTIONS BG_PER_EX BS BA S
------------ --------------- ---------- ---------- --- --- ---
0 254123311 2 7321.5 Y N Y

再次執行同樣的id=1的SQL。
SQL> exec :v := 1;
SQL> select /*comments*/ * from TESTBYHAO
2 where id = :v;
Plan hash value: 325910803
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 109 (100)| |
|* 1 | TABLE ACCESS FULL| TESTBYHAO | 104K| 2136K| 109 (4)| 00:00:02 |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("ID"=:V)

終於，我們期望的事情發生了，新的全表掃描的執行計劃產生了！（對應於CHILD_NUMBER=1,PLAN_HASH_VALUE=325910803）
v$SQL裡，新的cursor的IS_BIND_AWARE=Y。
CHILD_NUMBER PLAN_HASH_VALUE EXECUTIONS BG_PER_EX BS BA S
------------ --------------- ---------- ---------- --- --- ---
0 254123311 2 7321.5 Y N Y
1 325910803 1 7296 Y Y Y

再次執行id=1的SQL
SQL> exec :v := 1;
SQL> select /*comments*/ * from TESTBYHAO
2 where id = :v;

CHILD 1執行次數增加為2
CHILD_NUMBER PLAN_HASH_VALUE EXECUTIONS BG_PER_EX BS BA S
------------ --------------- ---------- ---------- --- --- ---
0 254123311 2 7321.5 Y N Y
1 325910803 2 7296 Y Y Y

再次執行id=2的SQL
SQL> exec :v := 2;
SQL> select /*comments*/ * from TESTBYHAO
2 where id = :v;

奇怪的事情發生了，又新生成了一個index range scan的cursor（CHILD 2），並且CHILD 0的IS_SHAREABLE=N了，表明這個cursor不再被使用了。
我想這是因為Oracle會監控每個cursor的平均selectivity，當新進來的繫結變數的cursor跟現有的cursor都差得比較遠時，就會新生成一個cursor，即使他們的執行計劃是有可能一樣的。
CHILD_NUMBER PLAN_HASH_VALUE EXECUTIONS BG_PER_EX BS BA S
------------ --------------- ---------- ---------- --- --- ---
0 254123311 2 7321.5 Y N N
1 325910803 2 7296 Y Y Y
2 254123311 1 73 Y Y Y

再次執行id=2的SQL
SQL> exec :v := 2;
SQL> select /*comments*/ * from TESTBYHAO
2 where id = :v;
CHILD 2 執行次數增加為2

CHILD_NUMBER PLAN_HASH_VALUE EXECUTIONS BG_PER_EX BS BA S
------------ --------------- ---------- ---------- --- --- ---
0 254123311 2 7321.5 Y N N
1 325910803 2 7296 Y Y Y
2 254123311 2 73 Y Y Y
更換繫結變數id=999，再次執行。
SQL> exec :v := 999;
SQL> select /*comments*/ * from TESTBYHAO
2 where id = :v;
果然，新的cursor CHILD 3出生了，雖然他依然使用的是index range scan，但它的selectivity是0。
這時，CHILD 2又“死”了。(IS_SHAREABLE=N)
CHILD_NUMBER PLAN_HASH_VALUE EXECUTIONS BG_PER_EX BS BA S
------------ --------------- ---------- ---------- --- --- ---
0 254123311 2 7321.5 Y N N
1 325910803 2 7296 Y Y Y
2 254123311 2 73 Y Y N
3 254123311 1 2 Y Y Y

使用id=2再來試試。
SQL> exec :v := 2;
SQL> select /*comments*/ * from TESTBYHAO
2 where id = :v;
使用了新的CHILD 3的cursor。
SQL> /
CHILD_NUMBER PLAN_HASH_VALUE EXECUTIONS BG_PER_EX BS BA S
------------ --------------- ---------- ---------- --- --- ---
0 254123311 2 7321.5 Y N N
1 325910803 2 7296 Y Y Y
2 254123311 2 73 Y Y N
3 254123311 2 37.5 Y Y Y

再換個繫結變數id=111試試。
SQL> exec :v := 111;
SQL> select /*comments*/ * from TESTBYHAO
2 where id = :v;
依然使用了CHILD 3，看來現在執行計劃基本處於一種穩定的狀態了。
SQL> /
CHILD_NUMBER PLAN_HASH_VALUE EXECUTIONS BG_PER_EX BS BA S
------------ --------------- ---------- ---------- --- --- ---
0 254123311 2 7321.5 Y N N
1 325910803 2 7296 Y Y Y
2 254123311 2 73 Y Y N
3 254123311 3 25.6666667 Y Y Y

再使用id=1來試試
SQL> exec :v := 1;
SQL> select /*comments*/ * from TESTBYHAO
2 where id = :v;
果然，CHILD 1被使用。
CHILD_NUMBER PLAN_HASH_VALUE EXECUTIONS BG_PER_EX BS BA S
------------ --------------- ---------- ---------- --- --- ---
0 254123311 2 7321.5 Y N N
1 325910803 3 7296 Y Y Y
2 254123311 2 73 Y Y N
3 254123311 3 25.6666667 Y Y Y
接著，我們來關注一下三個ACS的檢視。
1.v$sql_cs_histogram
SQL> SELECT CHILD_NUMBER,BUCKET_ID,COUNT FROM v$sql_cs_histogram
2 WHERE HASH_VALUE = '1659091011' order by 1,2,3;
CHILD_NUMBER BUCKET_ID COUNT
------------ ---------- ----------
0 0 1
0 1 1
0 2 0
1 0 0
1 1 3
1 2 0
2 0 2
2 1 0
2 2 0
3 0 3
3 1 0
3 2 0
這個檢視對於每個Child Cursor有三個buckets，用來計算每個cursor被執行的次數。
2.v$sql_cs_selectivity
SQL> l
1 SELECT CHILD_NUMBER,PREDICATE,RANGE_ID,LOW,HIGH FROM
2* v$sql_cs_selectivity WHERE HASH_VALUE = '1659091011'
SQL> /
CHILD_NUMBER PREDICATE RANGE_ID LOW HIGH
------------ ---------- ---------- ------------------------------ ------------------------------
3 =V 0 0.001705 0.004070
2 =V 0 0.003330 0.004070
1 =V 0 0.896589 1.095831
這個檢視顯示對於每種Child Cursor，它最高和最低的selectivity是多少。
這是因為Oracle不會也不可能對每個繫結變數都產生一個Child Cursor，那麼不同繫結變數就得根據自身的selectivity來在已有的Child Cursor中尋找，是否有比較接近的選擇率，如果有，那麼就重用這個cursor；否則，就如前面我的實驗一樣，新的Child Cursor就會孕育而生。
3.v$sql_cs_statistics
SQL> SELECT CHILD_NUMBER,BIND_SET_HASH_VALUE,PEEKED,EXECUTIONS,
2 ROWS_PROCESSED,BUFFER_GETS,CPU_TIME
3 FROM v$sql_cs_statistics WHERE HASH_VALUE = '1659091011';
CHILD_NUMBER BIND_SET_HASH_VALUE PEE EXECUTIONS ROWS_PROCESSED BUFFER_GETS CPU_TIME
------------ ------------------- --- ---------- -------------- ----------- ----------
3 3028748107 Y 1 0 2 0
2 2064090006 Y 1 996 73 0
1 2342552567 Y 1 104096 7296 0
0 2064090006 Y 1 996 221 0
這個檢視故名思意，顯示的是各個Child Cursor的統計資訊，例如是不是使用了繫結變數窺視，返回行數有多少，邏輯IO有多少等等。
如果需要檢視到底是什麼繫結變數產生的這些cursor，可以使用如下SQL查詢v$sql_bind_capture：
SQL> SELECT CHILD_NUMBER,VALUE_STRING,LAST_CAPTURED
2 FROM v$sql_bind_capture WHERE HASH_VALUE = '1659091011' order by 1;
CHILD_NUMBER VALUE_STRI LAST_CAPTURED
------------ ---------- -----------------
0 2 20091203 05:37:11
1 1 20091203 05:39:23
2 2 20091203 05:42:18
3 999 20091203 05:43:15
如何關閉ACS？
alter system set "_optimizer_extended_cursor_sharing_rel"=none;
alter system set "_optimizer_extended_cursor_sharing"=none;
alter system set "_optimizer_adaptive_cursor_sharing"=false;
證明：
SQL> alter session set "_optimizer_extended_cursor_sharing_rel"=none;
SQL> alter session set "_optimizer_extended_cursor_sharing"=none;
SQL> alter session set "_optimizer_adaptive_cursor_sharing"=false;
SQL> alter system flush shared_pool;
SQL> var v number;
SQL> exec :v :=2;
SQL> select /*comments*/ * from TESTBYHAO
2 where id = :v;
CHILD_NUMBER PLAN_HASH_VALUE EXECUTIONS BG_PER_EX BS BA S
------------ --------------- ---------- ---------- --- --- ---
0 254123311 1 286 N N Y
SQL> exec :v := 1;
SQL> select /*comments*/ * from TESTBYHAO
2 where id = :v;
CHILD_NUMBER PLAN_HASH_VALUE EXECUTIONS BG_PER_EX BS BA S
------------ --------------- ---------- ---------- --- --- ---
0 254123311 2 7354 N N Y
SQL> exec :v := 1;
SQL> select /*comments*/ * from TESTBYHAO
2 where id = :v;

CHILD_NUMBER PLAN_HASH_VALUE EXECUTIONS BG_PER_EX BS BA S
------------ --------------- ---------- ---------- --- --- ---
0 254123311 3 9710 N N Y
可見，當我在session級別關閉這三個隱藏引數後，IS_BIND_SENSITIVE始終為N，更換繫結變數後也不會產生新的cursor。
所以，當我們再做11GR2升級時，可以先考慮關閉這三個引數謀求SQL PLAN的穩定的同時，使用其他11G的new feature。
畢竟對於高併發的OLTP資料庫，穩定重於一切。
使用hint強制BIND_AWARE
在我研究11G所有新hint時，發現了這個hint：BIND_AWARE。
於是，就有了研究ACS的衝動，才有了這篇文章。
這個hint故名思意，會強制SQL產生BIND_AWARE的cursor。
更加強悍的是，即使你如上面第三點所示關閉了這三個ACS的引數，但hint依舊生效！
我先如上在session級別關閉這三個ACS的引數，然後進行了如下實驗。
SQL> exec :v := 1;
SQL> select /*comments*/ * from TESTBYHAO
2 where id = :v;
我們先發現，IS_BIND_AWARE=N。
CHILD_NUMBER PLAN_HASH_VALUE EXECUTIONS BG_PER_EX BS BA S
------------ --------------- ---------- ---------- --- --- ---
0 325910803 1 7515 N N Y
接著加上BIND_AWARE這個hint：
SQL> select /*+BIND_AWARE*/ * from TESTBYHAO
2 where id = :v;
可以看見區別了吧，IS_BIND_SENSITIVE=Y，IS_BIND_AWARE=Y。

CHILD_NUMBER PLAN_HASH_VALUE EXECUTIONS BG_PER_EX BS BA S
------------ --------------- ---------- ---------- --- --- ---
0 325910803 1 7296 Y Y Y
接下來更換繫結變數再run幾次，結果就是我們所熟悉的了。
再重申下，這是在我關閉session級別的ACS引數後進行的測試。
可見，BIND_AWARE這個hint很強悍。
CHILD_NUMBER PLAN_HASH_VALUE EXECUTIONS BG_PER_EX BS BA S
------------ --------------- ---------- ---------- --- --- ---
0 325910803 2 7296 Y Y Y
1 254123311 2 73 Y Y N
2 254123311 1 2 Y Y Y
於是，我想到了這樣的假設的情況，當我們升級到11GR2後，由於對ACS不瞭解，不敢用，於是再系統級別關閉了ACS的三個引數。但是突然某一天，我發現了一個由於data skew並且採用繫結變數的SQL PLAN不好調整時，我可以讓開發人員對這個特定的SQL加上這個hint，讓其突破關閉ACS的限制，使用ACS。於是，這似乎可以成為新的SQL tunning的好方法。
萬能的outline強於一切
本來寫完前四點就想結束了，但突然想到我們現有的系統上使用了無數的outline來固定SQL PLAN。那麼如果升級到11GR2後，在ACS的強大統治力下，outline會不會失效呢？
帶著這個疑問，我做了如下的實驗，結論是：outline強於一切！甚至可以突破BIND_AWARE這個強有力的hint的限制！
實驗一：不使用BIND_AWARE這個hint
SQL> select /*comment*/ * from TESTBYHAO
2 where id = :v;
先如願產生一個ACS會生效的cursor：CHILD_NUMBER PLAN_HASH_VALUE EXECUTIONS BG_PER_EX BS BA S
------------ --------------- ---------- ---------- --- --- ---
0 325910803 1 7519 Y N Y
使用outline固定這個SQL。
alter session set current_schema=HAOZHU_USER;
create outline ol_4107335673 for category temp_plan on
select /*comment*/ * from TESTBYHAO
where id = :v ;
alter session set current_schema=HAOZHU_USER;
create outline ol_temp4107335673 for category temp_plan on
select /*+full(TESTBYHAO)*/ /*comment*/ * from TESTBYHAO
where id = :v;
再exchange這兩個outline。
接著換id=2再次執行同樣的SQL：
SQL> exec :v:=2
SQL> select /*comment*/ * from TESTBYHAO
2 where id = :v ;
結果v$SQL裡產生了一個新的cursor（新的HASH_VALUE），並不是先前的cursor了，也不是先前的Child Cursor。
再多次執行上面的id=2的同樣的SQL後，我們可以看到：
SQL> /
CHILD_NUMBER PLAN_HASH_VALUE EXECUTIONS BG_PER_EX BS BA S
------------ --------------- ---------- ---------- --- --- ---
0 325910803 4 461.75 N N Y
可見，使用outline後，即使你開啟了ACS，ACS也不生效！
實驗二：使用BIND_AWARE hint
接著有人會問，你第四點提到的BIND_AWARE這個hint這麼強大，能夠突破關閉ACS的限制，那麼能否突破outline的限制呢？
帶著這個疑問，我做了如下實驗：
先使用BIND_AWARE hint，走index range scan：
SQL> select /*+BIND_AWARE*/ /*comment*/ * from TESTBYHAO
2 where id = :v;
CHILD_NUMBER PLAN_HASH_VALUE EXECUTIONS BG_PER_EX BS BA S
------------ --------------- ---------- ---------- --- --- ---
0 254123311 1 73 Y Y Y
如上，可見三個“Y”看著十分地舒服。
我接著建立outline固定使用全表掃描而不走index。
alter session set current_schema=HAOZHU_USER;
create outline ol_new for category temp_plan on
select /*+BIND_AWARE*/ /*comment*/ * from TESTBYHAO
where id = :v;
alter session set current_schema=HAOZHU_USER;
create outline ol_tempnew for category temp_plan on
select /*+FULL(TESTBYHAO)*/ /*+BIND_AWARE*/ /*comment*/ * from TESTBYHAO
where id = :v;
exchange這兩個outline。
然後再run一模一樣的這個SQL：
SQL> /
498 rows selected.
Note
-----
- outline "OL_NEW" used for this statement
接著看v$SQL裡：
CHILD_NUMBER PLAN_HASH_VALUE EXECUTIONS BG_PER_EX BS BA S
------------ --------------- ---------- ---------- --- --- ---
0 325910803 1 584 N N Y
果然，新的執行計劃出現了，代替了原來的那個執行計劃。
執行次數還是1，意味者前面的cursor被flush出去了，這是一個嶄新的cursor。
IS_BIND_AWARE=N，IS_BIND_SENSITIVE=N意味著這個SQL不受ACS控制了！

結語：本來對於ACS這個有趣的特性還有無數的實驗要做。比如我心裡還有十萬個為什麼：SQL profile受不受ACS控制？那三個ACS的隱藏引數每個的具體作用是什麼？ACS的選擇率是如何計算出來的？為何有大於1的選擇率。。。