11G以前ORACLE處理帶有繫結變數的SQL存在一些問題，由於SQL在硬解析時使用了繫結變數窺探，產生出的執行計劃不是對所有值都好，取決於第一次硬解析時窺探到的值,產生出的執行計劃有一定的隨機性，11GR1版本推出了Adaptive Cursor Sharing,後面簡稱ACS，用來解決繫結變數窺探帶來的問題，提升SQL語句的執行效能。最佳化器在使用ACS技術後，已經可以為一個帶有繫結變數的SQL產生出多個執行計劃（如果必要的話）。這個特性對於有資料傾斜的列非常有用，而在11G之前，一個SQL在一個特定的最佳化器環境下，只可能有一個執行計劃（在不同的最佳化器環境下，可能存在多個執行計劃，如：first_rows,all_rows）。本章會將為你啟開ACS的神秘面紗，為你揭示ACS的技術內幕，透過本章幾小節的學習，帶你深入瞭解ACS的方方面面。

ACS初探

ACS預設是啟用的，當然你可以透過一些隱含引數來開啟或關閉ACS,ACS的出現也引入了一些新檢視和新列。

l 兩個新列在V$SQL檢視裡：IS_BIND_SENSITIVE 和 IS_BIND_AWARE， IS_BIND_SENSITIVE的值可以為Y和N，為Y的cursor，代表這個cursor被ACS監控了，為N的cursor代表沒有被監控或者沒有啟用ACS功能。而IS_BIND_AWARE為Y的，代表這個cursor每次解析時都要窺探繫結變數的值，計算謂詞的選擇率然後根據選擇率檢視是否當前共享池中是否有滿足要求的執行計劃，如果有則重用，如果沒有，就要硬解析重新生成一個。

l V$SQL_CS_HISTOGRAM檢視，此檢視是ACS的關鍵檢視，主要記錄SQL處理的行數的直方圖，處理的行數驅動著ACS發揮作用。每個子cursor在這個檢視裡有3個bucket可用，編號從0-2，欄位bucket_id表示桶號，bucket_id 從0-2在每次SQL執行結束後，根據SQL處理行的數量，V$SQL_CS_HISTOGRAM中對應記錄的count會發生變化，count代表執行的次數，每一個bucket代表著cursor操作的資料量範圍，在11GR2版本，當返回的行數在0-1000時候，SQL執行完成後會更新在bucket_id 為0的桶的count欄位，每執行一次， count欄位的值會加1，當返回的行數在1000-1000000，SQL執行完成後會更新在bucket_id為1的桶的count欄位，返回的行數超過1000000，更新在bucket_id為2的桶的count欄位。請讀者不要死記數字，以後的版本可能會不同。如果SQL處理的行數發生鉅變，也就是說處理的行數散落在了至少2個桶內，下一次解析時，就要窺探繫結變數的值，重新硬解析生成執行計劃。

l V$SQL_CS_SELECTIVITY，記錄遊標謂詞的選擇率範圍，只有被標註為bind aware的cursor才會在此檢視中記錄。一旦一個cursor被標註為bind aware，每次解析時都要窺探繫結變數值，計算謂詞選擇率，然後根據計算的結果與此檢視中的相關記錄做比對，如果計算的選擇率落在了此檢視中對應遊標的選擇率範圍內，則軟解析，複用此遊標，如果不在，則硬解析，重新生成一個子遊標。文章有一節會專門針對此問題進行探討。

l V$SQL_CS_STATISTICS 記錄遊標處理的行數、buffer gets等資訊，但是此檢視具有一定的誤導性，檢視裡的資料只有在硬解析有新遊標產生的時候才會發生變化，軟解析的時候，這些指標值不會發生變化。此檢視一旦有新的行產生代表著SQL又重新產生了一個新的cursor，新產生的cursor是ACS起作用的結果。

理論的東西太枯燥，我們先來看一個測試示例。再回頭來看這些理論就會很容易理解。

test@DLSP>CREATE TABLE test

2 AS

3 SELECT ROWNUM id,

4 DBMS_RANDOM.STRING('A', 12) name,

5 DECODE(MOD(ROWNUM, 500), 0, 'Inactive', 'Active') status

6 FROM all_objects a,dba_objects b

7 WHERE ROWNUM <= 50000;

Table created.

SQL>CREATE INDEX test_id_ind ON test(status);

Index created.

SQL>begin

2 dbms_stats.gather_table_stats(user,

3 'test',

4 method_opt => 'for columns status size 2',

5 cascade => true);

6 end;

7 /

PL/SQL procedure successfully completed.

SQL>SELECT COUNT (*) cnt, status

2 FROM test

3 GROUP BY status

4 /

CNT STATUS

---------- ----------------

49900 Active

100 Inactive

上面的程式碼建立了一張測試表test，表上有一列status資料有傾斜，此列上建立了索引，收集表的統計資訊，並收集列status的直方圖。表中大部分的資料status為Active，極少的資料status為Inactive。我們可以開始我們的測試了，透過測試在針對列status不同值做查詢的情況下，幾個相關檢視中值的變化。

SQL>var a varchar2(100)
SQL>exec :a :='Inactive';

PL/SQL procedure successfully completed.

SQL>select /*+ find_me */ count(name) from test where status=:a;

COUNT(NAME)

-----------

100

1 row selected.

SQL>select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT

-------------------------------------------------------------------------------

SQL_ID a9cf9a1ky3bda, child number 0

-------------------------------------

select /*+ find_me */ count(name) from test where status=:a

Plan hash value: 2948918962

--------------------------------------------------------------------------------------------

--------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT| | | | 2 (100)| |

| 1 | SORT AGGREGATE| | 1 | 25 | | |

| 2 | TABLE ACCESS BY INDEX ROWID| TEST | 87| 2175 | 2 (0)| 00:00:01 |

|* 3 |INDEX RANGE SCAN |TEST_ID_IND| 87| | 1 (0)| 00:00:01 |

--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

3 - access("STATUS"=:A)

SQL> -- 檢查ACS狀態

SQL>SELECT child_number, executions, buffer_gets, is_bind_sensitive,

2 is_bind_aware

3 FROM v$sql

4 WHERE sql_id='a9cf9a1ky3bda';

CHILD_NUMBER EXECUTIONS BUFFER_GETS IS IS

------------ ---------- ----------- -- --

0 1 148 Y N

SQL>-- 直方圖

SQL>SELECT hash_value, sql_id, child_number, bucket_id, COUNT

2 FROM v$sql_cs_histogram

3 WHERE sql_id='a9cf9a1ky3bda'

4 ORDER BY sql_id, child_number;

HASH_VALUE SQL_ID CHILD_NUMBER BUCKET_ID COUNT

---------- --------------- ------------ ---------- ----------

1709288874 a9cf9a1ky3bda 0 0 1

1709288874 a9cf9a1ky3bda 0 2 0

1709288874 a9cf9a1ky3bda 0 1 0

SQL>-- 統計資訊

SQL>SELECT hash_value, sql_id, child_number, executions,

2 rows_processed

3 FROM v$sql_cs_statistics

4 WHERE sql_id='a9cf9a1ky3bda'

5 ORDER BY sql_id, child_number;

HASH_VALUE SQL_ID CHILD_NUMBER EXECUTIONS ROWS_PROCESSED

---------- --------------- ------------ ---------- --------------

1709288874 a9cf9a1ky3bda 0 1 201

SQL>

SQL>-- 選擇率

SQL>SELECT hash_value, sql_id, child_number, predicate, range_id, low, high

2 FROM v$sql_cs_selectivity

3 WHERE sql_id='a9cf9a1ky3bda'

4 ORDER BY sql_id, child_number;

no rows selected

從上面的輸出我們看到了，v$SQL的IS_BIND_SENSITIVE ='Y'，最佳化器已經標註此SQL為bind sensitive，最佳化器參考直方圖等統計資訊後，使用了索引掃描INDEX RANGE SCAN，由於Inactive值非常少，因此這是一個正確的決定，注意v$sql的輸出和其他V$檢視的輸出，由於此SQL處理的行數是100，因此檢視v$sql_cs_histogram中桶號bucket_id為0的行的count欄位發生了變化，值從0變為了1，代表執行了1次。這裡需要注意，最佳化器對繫結敏感的SQL需要在每次SQL執行結束後，更新v$sql_cs_histogram檢視中對應桶的count欄位。

SQL>exec :a :='Active'

PL/SQL procedure successfully completed.

SQL>select /*+ find_me */ count(name) from test where status=:a;

COUNT(NAME)

-----------

49900

1 row selected.

SQL>select * from table(dbms_xplan.display_cursor(null,null));

PLAN_TABLE_OUTPUT

-------------------------------------------------------------------------------

SQL_ID a9cf9a1ky3bda, child number 0

-------------------------------------

select /*+ find_me */ count(name) from test where status=:a

Plan hash value: 2948918962

--------------------------------------------------------------------------------------------

--------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 2 (100) | |

| 1 | SORT AGGREGATE | |1 | 25 | | |

| 2 |TABLE ACCESS BY INDEX ROWID| TEST |87 | 2175 | 2 (0)| 00:00:01|

|* 3 |INDEX RANGE SCAN | TEST_ID_IND |87 | | 1 (0)| 00:00:01|

--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

3 - access("STATUS"=:A)

SQL> -- 檢查ACS狀態

SQL>SELECT child_number, executions, buffer_gets, is_bind_sensitive,

2 is_bind_aware

3 FROM v$sql

4 WHERE sql_id='a9cf9a1ky3bda';

CHILD_NUMBER EXECUTIONS BUFFER_GETS IS IS

------------ ---------- ----------- -- --

0 2 482 Y N

SQL>

SQL>-- 直方圖

SQL>SELECT hash_value, sql_id, child_number, bucket_id, COUNT

2 FROM v$sql_cs_histogram

3 WHERE sql_id='a9cf9a1ky3bda'

4 ORDER BY sql_id, child_number;

HASH_VALUE SQL_ID CHILD_NUMBER BUCKET_ID COUNT

---------- --------------- ------------ ---------- ----------

1709288874 a9cf9a1ky3bda 0 0 1

1709288874 a9cf9a1ky3bda 0 2 0

1709288874 a9cf9a1ky3bda 0 1 1

SQL>

SQL>-- 統計資訊

SQL>SELECT hash_value, sql_id, child_number, executions,

2 rows_processed

3 FROM v$sql_cs_statistics

4 WHERE sql_id='a9cf9a1ky3bda'

5 ORDER BY sql_id, child_number;

HASH_VALUE SQL_ID CHILD_NUMBER EXECUTIONS ROWS_PROCESSED

---------- --------------- ------------ ---------- --------------

1709288874 a9cf9a1ky3bda 0 1 201

SQL>

SQL>-- 選擇率

SQL>SELECT hash_value, sql_id, child_number, predicate, range_id, low, high

2 FROM v$sql_cs_selectivity

3 WHERE sql_id='a9cf9a1ky3bda'

4 ORDER BY sql_id, child_number;

no rows selected

我們看到我們查詢status為Active時，v$sql中並沒有新的遊標產生，最佳化器複用了跟status為Inactive一樣的執行計劃，這個執行計劃是很糟糕的，因為status為Active的有49900個，佔了表裡的大部分資料，走全表掃描更好。仔細觀察輸出，我們注意到前後兩次v$sql_cs_histogram的輸出差異。第一次輸出的值，只有bucket_id為0記錄的count為1，第二次輸出的值，bucket_id為0的記錄值保持不變，bucket_id為1的的count已經從0變為了1，說明最佳化器已經意識到了這個SQL的處理的行數跟第一次已經大大的不同了。就像本章一開頭說到的，本次處理的行數已經超過了10000，所以SQL執行結束後更新了bucket_id為1的記錄裡的count的值。

我們繼續拿Active作為查詢值看看會出現什麼結果：

SQL>exec :a :='Active'

PL/SQL procedure successfully completed.

SQL>select /*+ find_me */ count(name) from test where status=:a;

COUNT(NAME)

-----------

49900

SQL>select * from table(dbms_xplan.display_cursor(null,null));

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------------------

SQL_ID a9cf9a1ky3bda, child number 1

-------------------------------------

select /*+ find_me */ count(name) from test where status=:a

Plan hash value: 1950795681

---------------------------------------------------------------------------

---------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | | | 51 (100) | |

| 1 | SORT AGGREGATE | | 1 | 25 | | |

|* 2 | TABLE ACCESS FULL | TEST | 49909 | 1218K| 51 (2) | 00:00:01 |

---------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

2 - filter("STATUS"=:A)

SQL> -- 檢查ACS狀態

SQL>SELECT child_number, executions, buffer_gets, is_bind_sensitive,

2 is_bind_aware

3 FROM v$sql

4 WHERE sql_id='a9cf9a1ky3bda';

CHILD_NUMBER EXECUTIONS BUFFER_GETS IS IS

------------ ---------- ----------- -- --

0 2 482 Y N

1 1 210 Y Y

SQL>

SQL>-- 直方圖

SQL>SELECT hash_value, sql_id, child_number, bucket_id, COUNT

2 FROM v$sql_cs_histogram

3 WHERE sql_id='a9cf9a1ky3bda'

4 ORDER BY sql_id, child_number;

HASH_VALUE SQL_ID CHILD_NUMBER BUCKET_ID COUNT

---------- --------------- ------------ ---------- ----------

1709288874 a9cf9a1ky3bda 0 1 1

1709288874 a9cf9a1ky3bda 0 0 1

1709288874 a9cf9a1ky3bda 0 2 0

1709288874 a9cf9a1ky3bda 1 1 1

1709288874 a9cf9a1ky3bda 1 0 0

1709288874 a9cf9a1ky3bda 1 2 0

6 rows selected.

SQL>

SQL>-- 統計資訊

SQL>SELECT hash_value, sql_id, child_number, executions,

2 rows_processed

3 FROM v$sql_cs_statistics

4 WHERE sql_id='a9cf9a1ky3bda'

5 ORDER BY sql_id, child_number;

HASH_VALUE SQL_ID CHILD_NUMBER EXECUTIONS ROWS_PROCESSED

---------- --------------- ------------ ---------- --------------

1709288874 a9cf9a1ky3bda 0 1 201

1709288874 a9cf9a1ky3bda 1 1 49901

SQL>

SQL>-- 選擇率

SQL>SELECT hash_value, sql_id, child_number, predicate, range_id, low, high

2 FROM v$sql_cs_selectivity

3 WHERE sql_id='a9cf9a1ky3bda'

4 ORDER BY sql_id, child_number;

HASH_VALUE SQL_ID CHILD_NUMBER PREDICATE RANGE_ID LOW HIGH

---------- --------------- ------------ ---------- ---------- ---------- ----------

1709288874 a9cf9a1ky3bda 1 =A 0 0.898361 1.097996

最佳化器終於意識到自己犯了錯誤，重新硬解析產生了新的執行計劃，採用了full table scan。V$SQL已經產生了新遊標，IS_BIND_AWARE的值也已經變成了Y，檢視v$SQL_CS_HISTOGRAM額外的多了3行，為了記錄新遊標（child_numer為1）的處理行數直方圖資訊。由於有了硬解析，檢視V$SQL_CS_STATISTICS也新增了一行記錄新遊標的行處理資訊和buffer gets資訊。$SQL_CS_SELECTIVITY也有了記錄，記錄了新遊標謂詞的選擇率範圍。截止到目前，這個SQL已經是bind aware的了。bind aware意味著以後每次解析此SQL都要去窺探這個SQL的繫結變數值計算選擇率，然後根據計算的結果與v$sql_cs_selectivity檢視中的相關記錄做比對，如果計算的選擇率落在了此檢視中對應遊標的選擇率範圍內，則軟解析，複用此遊標，如果不在，則硬解析，重新生成一個子遊標。

SQL>var a varchar2(100)
SQL>exec :a :='Inactive'

PL/SQL procedure successfully completed.

SQL>select /*+ find_me */ count(name) from test where status=:a;

COUNT(NAME)

-----------

100

SQL> -- 檢查ACS狀態

SQL>SELECT child_number, executions, buffer_gets, is_bind_sensitive,

2 is_bind_aware

3 FROM v$sql

4 WHERE sql_id='a9cf9a1ky3bda';

CHILD_NUMBER EXECUTIONS BUFFER_GETS IS IS

------------ ---------- ----------- -- --

0 2 482 Y N

1 1 210 Y Y

2 1 102 Y Y

SQL>-- 直方圖

SQL>SELECT hash_value, sql_id, child_number, bucket_id, COUNT

2 FROM v$sql_cs_histogram

3 WHERE sql_id='a9cf9a1ky3bda'

4 ORDER BY sql_id, child_number;

HASH_VALUE SQL_ID CHILD_NUMBER BUCKET_ID COUNT

---------- --------------- ------------ ---------- ----------

1709288874 a9cf9a1ky3bda 0 1 1

1709288874 a9cf9a1ky3bda 0 0 1

1709288874 a9cf9a1ky3bda 0 2 0

1709288874 a9cf9a1ky3bda 1 0 0

1709288874 a9cf9a1ky3bda 1 1 1

1709288874 a9cf9a1ky3bda 1 2 0

1709288874 a9cf9a1ky3bda 2 1 0

1709288874 a9cf9a1ky3bda 2 0 1

1709288874 a9cf9a1ky3bda 2 2 0

9 rows selected.

SQL>-- 統計資訊

SQL>SELECT hash_value, sql_id, child_number, executions,

2 rows_processed

3 FROM v$sql_cs_statistics

4 WHERE sql_id='a9cf9a1ky3bda'

5 ORDER BY sql_id, child_number;

HASH_VALUE SQL_ID CHILD_NUMBER EXECUTIONS ROWS_PROCESSED

---------- --------------- ------------ ---------- --------------

1709288874 a9cf9a1ky3bda 0 1 201

1709288874 a9cf9a1ky3bda 1 1 49901

1709288874 a9cf9a1ky3bda 2 1 201

SQL>-- 選擇率

SQL>SELECT hash_value, sql_id, child_number, predicate, range_id, low, high

2 FROM v$sql_cs_selectivity

3 WHERE sql_id='a9cf9a1ky3bda'

4 ORDER BY sql_id, child_number;

HASH_VALUE SQL_ID CHILD_NUMBER PREDICATE RANGE_ID LOW HIGH

---------- --------------- ------------ ---------- ---------- ---------- ----------

1709288874 a9cf9a1ky3bda 1 =A 0 0.898361 1.097996

1709288874 a9cf9a1ky3bda 2 =A 0 0.001557 0.001903

由於這個SQL已經是bind aware的了，當再次執行status='Inactive'時，最佳化器窺探到傳入的值並參考直方圖資訊等統計資訊，計算選擇率與現有的遊標的選擇率不符，因此重新硬解析產生了一個新的遊標，這個遊標的is_bind_aware欄位為'Y'，產生了index range scan的執行計劃，非常棒！檢視v$sql_cs_histogram，v$sql_cs_statistics，v$sql_cs_selectivity也都有相應的變化。

從上面的實驗中，我們可以知道，ACS存在著不穩定期，必須在一個遊標的效能變糟後，最佳化器才能意識到犯了錯誤，再下次執行時嘗試糾正錯誤。觸發這一行為是依靠在v$sql_cs_histogram檢視中，此遊標的3個桶中出現了兩個桶中的count都有非0值，一旦被觸發，在每次解析階段，都要去窺探繫結變數的值計算選擇率，如果計算選擇率與現有的遊標的選擇率不符，就會基於窺探到的繫結變數的值硬解析重新產生了一個新的遊標，並且此SQL會被標註為bind aware。

Adaptive Cursor Sharing （第一篇）

ACS初探

相關文章