11G Adaptive Cursor Sharing(ACS)的研究

Davis_itpub發表於2018-06-27

    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的選擇率。。。

但是,由於今晚要去泰山team building,心有餘而力不足。還得準備吃的,所以就到此為止。留作以後再做研究。

 

      Hao

    2009-12-04

    寫於張江

 

來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/31520497/viewspace-2156807/,如需轉載,請註明出處,否則將追究法律責任。

相關文章