Oracle透過引入繫結變數解決了硬解析過多的問題，從一定程度上降低了對資源(共享池)的爭用。對於使用繫結變數的SQL語句，在第一次硬解析的時候會透過繫結窺探技術（Bind Peeking）確認繫結變數的輸入值，然後根據該值來確定執行計劃，並將執行計劃儲存在共享池，以便後續共享。此方案一般情況執行良好，但對於一些特殊情況比如某列值分佈十分傾斜，會造成比較嚴重的效能問題，因為SQL的最佳執行計劃已經改變了，再採用第一次硬解析的執行計劃明顯是不明智的或者錯誤的。
為了彌補Bind Peeking的缺陷，從11g開始Oracle引入了自適應遊標共享機制（ACS），該技術將繫結變數的執行計劃變為一個基於統計量分析的自適應過程，根據繫結變數的具體輸入值動態的生成執行計劃。
自適應遊標整體執行流程如下：
1.當SQL第一次被執行時，Oracle會用硬解析，同時Oracle會根據情況來判斷是否將SQL所對應的Child Cursor標記為Bind Sensitive.標記為Sensitive說明後續該SQL的執行計劃可能會改變。
什麼情況下,SQL所對應的Child Cursor會比較為Bind Sensitive呢？滿足如下條件
1)啟用了繫結變數窺探
2)該SQL使用了繫結變數
3)該SQL使用的是不安全的謂詞條件(範圍查詢、目標列上有直方圖統計資訊的等值查詢等)
2.當SQL第二次被執行時，Oracle會用軟解析，重用該SQL第一次執行時所產生的Child Cursor中儲存的解析樹和執行計劃。
3.當SQL第三次被執行，若該SQL所產生的Child Cursor已經被標記為Bind Sensitive，同時Oracle在第二次和第三次執行該SQL記錄的runtime統計資訊和第一次硬解析所記錄的runtime統計資訊存在較大差異，則該SQL在第三次被執行會使用硬解析，Oracle此時會產生一個新的Child Cursor，並且把這個新的Child Cursor標記為Bind Aware.
什麼叫"Bind Aware"?通俗來講，Bind Aware指Oracle已經非常明確的確定這個包含繫結變數的SQL，其執行計劃會隨著繫結變數輸入值的變化而變化。
4. 對於標記為Bind Aware的Child Cursor所對應的SQL，當該SQL再次被執行時，Oracle會根據當前傳入的繫結變數值所對應的謂詞條件的選擇率，來決定此時是硬解析還是軟解析(軟軟解析)。
具體原則為當傳入的繫結變數值所在的謂詞條件的選擇率處於v$SQL_CS_STATISTICS中記錄的選擇率範圍內，此時Oracle會使用軟解析或者軟軟解析，否則會硬解析。如果為硬解析，則Oracle會重新生成一個Child Cursor，另外還會把原有的Child Cursor標記為非共享，以便在共享池資源緊張的時候，第一時間將非共享遊標刷出並覆蓋。另外如果此次硬解析所產生的執行計劃和原有Child Cursor中儲存的執行計劃相同，Oracle會將原Child Cursor同新的Child Cursor進行合併（主要指選擇率的合併）。
注：上面內容為了便於理解簡化了部分細節，其實只要後續runtime統計資訊差異較大的SQL執行次數大於之前的SQL執行次數，下一次該SQL的執行就會使用硬解析。
下面透過具體的實驗進行說明：

點選(此處)摺疊或開啟

//資料庫版本資訊
SELECT * FROM v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE 11.2.0.3.0 Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production

//建立表
create table tab_acs(id int,value int);
//插入測試資料
begin
for i in 1 .. 20000
loop
execute immediate 'insert into tab_acs values(1,'||i||')';
end loop;
end;
/
begin
for i in 1 .. 10
loop
execute immediate 'insert into tab_acs values(2,:1)' using i;
end loop;
end;
/

commit;

//確認資料的分佈情況
select id,count(*) from tab_acs group by id;
ID COUNT(*)
---------- ----------
1 20000
2 10

//建立索引
create index idx_tab_acs on tab_acs(id);
//收集統計資訊
exec dbms_stats.gather_table_stats(user,'TAB_ACS',cascade=>true,METHOD_OPT=>'FOR ALL COLUMNS');
//檢視統計資訊情況
SQL> select table_name,column_name,histogram from dba_tab_col_statistics where table_name='TAB_ACS';
TABLE_NAME COLUMN_NAME HISTOGRAM
------------------------------ ------------------------------ ---------------
TAB_ACS VALUE HEIGHT BALANCED
TAB_ACS ID FREQUENCY

好了，上面已完成測試環境的準備工作。下面進行ACS的測試，首先先看一下未使用繫結變數的執行計劃情況：

點選(此處)摺疊或開啟

select count(value) from tab_acs where id=1;
COUNT(VALUE)
------------
20000
select sql_text,sql_id,version_count,executions from v$sqlarea where sql_text like 'select count(value)%';
SQL_TEXT SQL_ID VERSION_COUNT EXECUTIONS
------------------------------------------------------------ ------------- ------------- ----------
select count(value) from tab_acs where id=1 f4jwtn3360ppk 1 1

select * from table(dbms_xplan.display_cursor('f4jwtn3360ppk',0,'advanced'));
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID f4jwtn3360ppk, child number 0
-------------------------------------
select count(value) from tab_acs where id=1
Plan hash value: 3684903434
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 11 (100)| |
| 1 | SORT AGGREGATE | | 1 | 7 | | |
|* 2 | TABLE ACCESS FULL| TAB_ACS | 20000 | 136K| 11 (0)| 00:00:01 |
------------------------------------------------------------------------------

select count(value) from tab_acs where id=2;
COUNT(VALUE)
------------
10

SQL> select sql_text,sql_id,version_count,executions from v$sqlarea where sql_text like 'select count(value)%';
SQL_TEXT SQL_ID VERSION_COUNT EXECUTIONS
------------------------------------------------------------ ------------- ------------- ----------
select count(value) from tab_acs where id=1 f4jwtn3360ppk 1 1
select count(value) from tab_acs where id=2 9vx4syymyzkb7 1 1

select * from table(dbms_xplan.display_cursor('9vx4syymyzkb7',0,'advanced'));
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID 9vx4syymyzkb7, child number 0
-------------------------------------
select count(value) from tab_acs where id=2

Plan hash value: 3029888215
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
| 1 | SORT AGGREGATE | | 1 | 7 | | |
| 2 | TABLE ACCESS BY INDEX ROWID| TAB_ACS | 10 | 70 | 2 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | IDX_TAB_ACS | 10 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------

可以看到謂詞條件為1時走全表掃描，謂詞條件為2的情況下走索引範圍掃描。

下面看一下使用繫結變數的執行計劃：
點選(此處)摺疊或開啟
1. var x number;
2. exec :x:=1;
4. select count(value) from tab_acs where id=:x;
  COUNT(VALUE)
  ------------
  20000
6. select sql_text,sql_id,child_number,executions,buffer_gets,is_bind_sensitive as "BS",is_bind_aware as "BA",is_shareable as "BSH",plan_hash_value from v$sql where sql_text
7. like 'select count(value)%';
  SQL_TEXT SQL_ID CHILD_NUMBER EXECUTIONS BUFFER_GETS B B B PLAN_HASH_VALUE
  ------------------------------------------------------------ ------------- ------------ ---------- ----------- - - - ---------------
  select count(value) from tab_acs where id=:x 5gy2wu883n8ac 0 1 96 Y N Y 3684903434
  
  exec :x:=2;
  select count(value) from tab_acs where id=:x;
  COUNT(VALUE)
  ------------
  10
  
  //根據前面的結論，本次肯定為軟解析
  select sql_text,sql_id,child_number,executions,buffer_gets,is_bind_sensitive as "BS",is_bind_aware as "BA",is_shareable as "BSH",plan_hash_value from v$sql where sql_text
  like 'select count(value)%';
  SQL_TEXT SQL_ID CHILD_NUMBER EXECUTIONS BUFFER_GETS B B B PLAN_HASH_VALUE
  ------------------------------------------------------------ ------------- ------------ ---------- ----------- - - - ---------------
  select count(value) from tab_acs where id=:x 5gy2wu883n8ac 0 2 133 Y N Y 3684903434
  
  :x變數設定為2進行第二次查詢
  select count(value) from tab_acs where id=:x;
  COUNT(VALUE)
  ------------
  10
  
  select sql_text,sql_id,child_number,executions,buffer_gets,is_bind_sensitive as "BS",is_bind_aware as "BA",is_shareable as "BSH",plan_hash_value from v$sql where sql_text
  like 'select count(value)%';
  SQL_TEXT SQL_ID CHILD_NUMBER EXECUTIONS BUFFER_GETS B B B PLAN_HASH_VALUE
  ------------------------------------------------------------ ------------- ------------ ---------- ----------- - - - ---------------
  select count(value) from tab_acs where id=:x 5gy2wu883n8ac 0 2 133 Y N N 3684903434
  select count(value) from tab_acs where id=:x 5gy2wu883n8ac 1 1 3 Y Y Y 3029888215
  
  既然CHILD_NUMBER=1標記為了BIND_AWARE,那麼v$sql_cs_selectivity肯定有相關數值
  
  select child_number,predicate,range_id,low,high from v$sql_cs_selectivity where sql_id='5gy2wu883n8ac';
  CHILD_NUMBER PREDICATE RANGE_ID LOW HIGH
  ------------ ---------------------------------------- ---------- ---------- ----------
  1 =X 0 0.000427 0.000522
  
  :x變數設定為3進行查詢
  exec :x:=3;
  select count(value) from tab_acs where id=:x;
  COUNT(VALUE)
  ------------
  0
  
  select sql_text,sql_id,child_number,executions,buffer_gets,is_bind_sensitive as "BS",is_bind_aware as "BA",is_shareable as "BSH",plan_hash_value from v$sql where sql_text
  like 'select count(value)%';
  SQL_TEXT SQL_ID CHILD_NUMBER EXECUTIONS BUFFER_GETS B B B PLAN_HASH_VALUE
  ------------------------------------------------------------ ------------- ------------ ---------- ----------- - - - ---------------
  select count(value) from tab_acs where id=:x 5gy2wu883n8ac 0 2 133 Y N N 3684903434
  select count(value) from tab_acs where id=:x 5gy2wu883n8ac 1 1 3 Y Y N 3029888215
  select count(value) from tab_acs where id=:x 5gy2wu883n8ac 2 1 2 Y Y Y 3029888215
  
  可以判斷:x:=3肯定走的索引範圍掃描，同:x:=2情況一樣（從查詢結果CHILD_NUMBER=1,2的PLAN_HASH_VALUE值相同也可以看出這一點），但由於選擇率不在v$sql_cs_selectivity範圍內，還會進行硬解析，因此產生
  了CHILD_NUMBER=2
  的CHILD CURSOR.
  select child_number,predicate,range_id,low,high from v$sql_cs_selectivity where sql_id='5gy2wu883n8ac';
  CHILD_NUMBER PREDICATE RANGE_ID LOW HIGH
  ------------ ---------------------------------------- ---------- ---------- ----------
  2 =X 0 0.000225 0.000522
  1 =X 0 0.000427 0.000522
  
  可以看到LOW,HIGH的範圍由[0.000427,0.000522]擴大到了[0.000225,0.000522].既然選擇率已經擴大到了[0.000225,0.000522]，可以斷定謂詞條件修改為:x:=10，肯定會重用CHILD_NUMBER=2的執行計劃，下面進行驗證：
  exec :x:=10;
  select count(value) from tab_acs where id=:x;
  
  COUNT(VALUE)
  ------------
  0
  
  select sql_text,sql_id,child_number,executions,buffer_gets,is_bind_sensitive as "BS",is_bind_aware as "BA",is_shareable as "BSH",plan_hash_value from v$sql where sql_text
  like 'select count(value)%';
  SQL_TEXT SQL_ID CHILD_NUMBER EXECUTIONS BUFFER_GETS B B B PLAN_HASH_VALUE
  ------------------------------------------------------------ ------------- ------------ ---------- ----------- - - - ---------------
  select count(value) from tab_acs where id=:x 5gy2wu883n8ac 0 2 133 Y N N 3684903434
  select count(value) from tab_acs where id=:x 5gy2wu883n8ac 1 1 3 Y Y N 3029888215
  select count(value) from tab_acs where id=:x 5gy2wu883n8ac 2 2 4 Y Y Y 3029888215
  
  因為謂詞條件:x:=1對應的CHILD CURSOR對應的共享標誌已經標記為N，那麼如果現在在對:x:=1進行查詢，因此其對應的選擇率不在CHILD_NUMBER=2的範圍內，因此肯定還會硬解析。
  exec :x:=1;
  select count(value) from tab_acs where id=:x;
  COUNT(VALUE)
  ------------
  20000
  
  select sql_text,sql_id,child_number,executions,buffer_gets,is_bind_sensitive as "BS",is_bind_aware as "BA",is_shareable as "BSH",plan_hash_value from v$sql where sql_text like
  'select count(value)%';
  SQL_TEXT SQL_ID CHILD_NUMBER EXECUTIONS BUFFER_GETS B B B PLAN_HASH_VALUE
  ------------------------------------------------------------ ------------- ------------ ---------- ----------- - - - ---------------
  select count(value) from tab_acs where id=:x 5gy2wu883n8ac 0 2 133 Y N N 3684903434
  select count(value) from tab_acs where id=:x 5gy2wu883n8ac 1 1 3 Y Y N 3029888215
  select count(value) from tab_acs where id=:x 5gy2wu883n8ac 2 2 4 Y Y Y 3029888215
  select count(value) from tab_acs where id=:x 5gy2wu883n8ac 3 1 37 Y Y Y 3684903434
  
  CHILD_NUMBER=3便是新生成的遊標，下面看一下v$sql_cs_selectivity肯定也有相關變化。
  select child_number,predicate,range_id,low,high from v$sql_cs_selectivity where sql_id='5gy2wu883n8ac';
  CHILD_NUMBER PREDICATE RANGE_ID LOW HIGH
  ------------ ---------------------------------------- ---------- ---------- ----------
  3 =X 0 0.899550 1.099450
  2 =X 0 0.000225 0.000522
  1 =X 0 0.000427 0.000522
  
  經過以上語句的訓練，共享池中生成了兩條比較穩定的儲存執行計劃的CHILD CURSOR，分別為CHILD_NUMBER 2和3.下次繫結變數有新值輸入，會根據選擇率的範圍決定走軟解析或者硬解析。
從上述對ACS自適應遊標共享的整個測試過程來看，自適應遊標在一定程度上緩解了繫結變數窺探所帶來的副作用，可以讓SQL在其可能的多個執行計劃之間自適應的做出選擇，而不再像
之前那樣必須得刻板得沿用SQL硬解析時所產生的解析樹和執行計劃。

繫結變數之自適應遊標共享(Adaptive Cursor Sharing)

相關文章