如何進行 Adaptive Cursor Sharing的研究

如何進行 Adaptive Cursor Sharing的研究，很多新手對此不是很清楚，為了幫助大家解決這個難題，下面小編將為大家詳細講解，有這方面需求的人可以來學習下，希望你能有所收獲。

    Adaptive Cursor Sharing（ACS）是又一個大膽而吸引人的11G新特性。

說它大膽是因為它試圖解決一個CBO最令人頭疼的問題：數據傾斜（data skew）和綁定變量窺視導致SQL PLAN太差。說它吸引人是因為想知道Oracle采用何種神秘的算法讓Oracle變得更加智能。

    之所以在11GR2出來之后才開始研究，是因為這個new feture在11GR1時有各種各樣的問題，首先映入我眼簾的就是這個bug：

Bug 7213010  Adaptive cursor sharing generates lots of child cursors

This issue is fixed in 11.2 (Future Release),11.1.0.7 (Server Patch Set)

    ACS其實就是根據不同綁定變量的值為同一個SQL生成更多更優的執行計劃，來適應data skew的不同情況。正因為如此，才會有如上的bug出現。

    <一> 我們先用一個簡單的例子來走近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的選擇率。。。

看完上述內容是否對您有幫助呢？如果還想對相關知識有進一步的了解或閱讀更多相關文章，請關注億速云行業資訊頻道，感謝您對億速云的支持。