自適應遊標共享是Oracle11g推出的一個新特性。透過ACS，我們可以一定程度上緩解由於bind peeking和cursor_sharing引數設定帶來的子游標共享問題。在前篇（http://space.itpub.net/17203031/viewspace-703280）中，我們已經觀察過Oracle 10g下的bind peeking和遊標共享，以及11g上的ACS特性。本篇，我們打算深入分析一下與ACS有關的檢視和引數，最後綜述一下ACS的優劣。

1、 ACS相關引數和檢視物件

Oracle ACS是有相應的技術背景的。在CBO環境下，ACS是解決bind peeking對bad SQL statement解析的一種方式。所以，存在相關係統引數與ACS對應。

ü optimizer_mode：最佳化器模式，表示選擇使用CBO還是RBO。在目前使用的10g和11g中，由於dynamic sampling技術的存在，大部分情況下，我們是在CBO最佳化模式下。通常使用choose就可以了；

ü optimizer_features_enable：使用最佳化器特性版本。啟用ACS使用11.2.0.1即可；

ü optimizer_capture_sql_plan_baselines=false：關閉SQL 執行計劃baseline管理機制；

ü cursor_sharing=EXACT：完全匹配的繫結變數機制；

ü _optim_peek_user_binds：是否開啟繫結變數機制；

之後的三個引數與ACS開啟與否相關：

ü _optimizer_adaptive_cursor_sharing=TRUE：

ü _optimizer_extended_cursor_sharing=UDO；

ü _optimizer_extended_cursor_sharing_rel=SIMPLE；

除了這些與ACS相關的引數，還新增加了三個度量檢視用於ACS的正常工作。

ü v$sql_cs_histogram：Oracle 11g使用分配的頻率確定SQL是否是bind sensitive。其中包括執行次數和落在查詢上的遊標個數；

ü v$sql_cs_selectitiy：包括有關SQL語句謂詞的相對選擇性資訊。也就是選擇率立方體；

ü v$sql_cs_statistics：列出自適應遊標是否被共享以及如何共享統計量資訊；

透過這些新增加的引數和度量檢視，Oracle實現了ACS的自適應過程。

2、 自適應遊標過程

下面我們進行試驗，來觀察對ACS的測算機制。注意：我們採用前篇相同的資料表和分佈情況。

SQL> select * from v$version;

BANNER

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

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

PL/SQL Release 11.2.0.1.0 - Production

CORE 11.2.0.1.0 Production

SQL> select dbms_stats.get_param('method_opt') from dual;

DBMS_STATS.GET_PARAM('METHOD_O

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

FOR ALL COLUMNS SIZE AUTO

對資料表收集統計資訊之後，頻度直方圖在id1列出現。說明統計量中已經包括了直方圖資訊。

SQL> select id1, count(*) from t group by id1;

ID1 COUNT(*)

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

U 20

K 10000

M 20

O 30000

SQL> select table_name, column_name, HISTOGRAM from dba_tab_col_statistics where wner='SYS' and table_name='T';

TABLE_NAME COLUMN_NAME HISTOGRAM

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

T ID1 FREQUENCY

T ID2 NONE

T ID3 NONE

取值原因，id1列的直方圖只有4個bucket。

SQL> select ENDPOINT_NUMBER, ENDPOINT_VALUE, ENDPOINT_ACTUAL_VALUE from dba_tab_histograms where table_name='T' and wner='SYS' and column_name='ID1';

ENDPOINT_NUMBER ENDPOINT_VALUE ENDPOINT_ACTUAL_VALUE

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

1407 3.894222643901

1414 3.998068581071

5594 4.101914518242

5596 4.413452329754

ü 第一次繫結變數測試

id1=’O’，篩選資料行30000行，應該走全表掃描路徑。

SQL> var i varchar2(10);

SQL> exec :i:='O';

PL/SQL 過程已成功完成。

已用時間: 00: 00: 00.01

SQL> select * from t where id1=:i;

已選擇30000行。

此時父子游標狀況為。生成了一個父遊標和第一個子游標。生成執行計劃過程中使用了bind peeking技術。

SQL> select sql_text, SQL_ID, VERSION_COUNT, VERSION_COUNT, executions, IS_BIND_SENSITIVE, IS_BIND_AWARE from v$sqlarea where sql_text like 'select * from t where id1=:i%';

SQL_TEXT SQL_ID VERSION_COUNT VERSION_COUNT EXECUTIONS IS_BIND_SENSITIVE IS_BIND_AWARE

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

select * from t where id1=:i a3y1yrq36v0gn 1 1 1 Y N

SQL> select sql_text, CHILD_NUMBER, IS_BIND_SENSITIVE, IS_BIND_AWARE, IS_SHAREABLE from v$sql where sql_id='a3y1yrq36v0gn';

SQL_TEXT CHILD_NUMBER IS_BIND_SENSITIVE IS_BIND_AWARE IS_SHAREABLE

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

select * from t where id1=:i 0 Y N Y

各列含義在前篇中已經陳述，不加以累述了。此時的執行計劃如下：

SQL> select * from table(dbms_xplan.display_cursor('a3y1yrq36v0gn',0,format => 'advanced'));

PLAN_TABLE_OUTPUT

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

SQL_ID a3y1yrq36v0gn, child number 0

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

select * from t where id1=:i

Plan hash value: 1601196873

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

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

| 0 | SELECT STATEMENT | | | | 19 (100)| |

|* 1 | TABLE ACCESS FULL| T | 29908 | 175K| 19 (0)| 00:00:01 |

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

Peeked Binds (identified by position):

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

1 - :I (VARCHAR2(30), CSID=873): 'O'

Predicate Information (identified by operation id):

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

1 - filter("ID1"=:I)

48 rows selected

使用了繫結變數’O’，採用了全表掃描執行計劃。三個CS檢視如下：

SQL> select * from v$sql_cs_histogram where sql_id='a3y1yrq36v0gn';

ADDRESS HASH_VALUE SQL_ID CHILD_NUMBER BUCKET_ID COUNT

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

433F59CC 2255323636 a3y1yrq36v0gn 0 0 0

433F59CC 2255323636 a3y1yrq36v0gn 0 1 1

433F59CC 2255323636 a3y1yrq36v0gn 0 2 0

SQL> select * from v$sql_cs_selectivity where sql_id='a3y1yrq36v0gn';

ADDRESS HASH_VALUE SQL_ID CHILD_NUMBER PREDICATE RANGE_ID LOW HIGH

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

SQL> select * from v$sql_cs_statistics where sql_id='a3y1yrq36v0gn';

ADDRESS HASH_VALUE SQL_ID CHILD_NUMBER BIND_SET_HASH_VALUE PEEKED EXECUTIONS ROWS_PROCESSED BUFFER_GETS CPU_TIME

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

433F59CC 2255323636 a3y1yrq36v0gn 0 3804331937 Y 1 30000 2094 0

第一次執行生成了編號為0的子游標，執行值對應落在bucket_id=1的計數上。

ü 第二次繫結變數測試

第二次選擇一個索引執行計劃。

SQL> exec :i:='U';

PL/SQL 過程已成功完成。

已用時間: 00: 00: 00.02

SQL> select * from t where id1=:i;

已選擇20行。

對應執行計劃資訊：

SQL> select sql_text, SQL_ID, VERSION_COUNT, VERSION_COUNT, executions, IS_BIND_SENSITIVE, IS_BIND_AWARE from v$sqlarea where sql_text like 'select * from t where id1=:i%';

SQL_TEXT SQL_ID VERSION_COUNT VERSION_COUNT EXECUTIONS IS_BIND_SENSITIVE IS_BIND_AWARE

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

select * from t where id1=:i a3y1yrq36v0gn 1 1 2 Y N

SQL> select sql_text, CHILD_NUMBER, IS_BIND_SENSITIVE, IS_BIND_AWARE, IS_SHAREABLE, executions from v$sql where sql_id='a3y1yrq36v0gn';

SQL_TEXT CHILD_NUMBER IS_BIND_SENSITIVE IS_BIND_AWARE IS_SHAREABLE EXECUTIONS

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

select * from t where id1=:i 0 Y N Y 2

注意！雖然這個執行顯然是走索引計劃比較好，但是Oracle並沒有為它生成索引執行計劃。統計量如下：

SQL> select * from v$sql_cs_histogram where sql_id='a3y1yrq36v0gn';

ADDRESS HASH_VALUE SQL_ID CHILD_NUMBER BUCKET_ID COUNT

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

433F59CC 2255323636 a3y1yrq36v0gn 0 0 1

433F59CC 2255323636 a3y1yrq36v0gn 0 1 1

433F59CC 2255323636 a3y1yrq36v0gn 0 2 0

SQL> select * from v$sql_cs_selectivity where sql_id='a3y1yrq36v0gn';

ADDRESS HASH_VALUE SQL_ID CHILD_NUMBER PREDICATE RANGE_ID LOW HIGH

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

SQL> select * from v$sql_cs_statistics where sql_id='a3y1yrq36v0gn';

ADDRESS HASH_VALUE SQL_ID CHILD_NUMBER BIND_SET_HASH_VALUE PEEKED EXECUTIONS ROWS_PROCESSED BUFFER_GETS CPU_TIME

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

433F59CC 2255323636 a3y1yrq36v0gn 0 3804331937 Y 1 30000 2094 0

第二次執行就落入了第一個bucket中。

ü 第三次繫結變數實驗

此時，我們再次使用’U’作為繫結變數取值進行呼叫。

SQL> select * from t where id1=:i;

已選擇20行。

已用時間: 00: 00: 00.03

此時，出現新的子游標對應。

SQL> select sql_text, SQL_ID, VERSION_COUNT, VERSION_COUNT, executions, IS_BIND_SENSITIVE, IS_BIND_AWARE from v$sqlarea where sql_text like 'select * from t where id1=:i%';

SQL_TEXT SQL_ID VERSION_COUNT VERSION_COUNT EXECUTIONS IS_BIND_SENSITIVE IS_BIND_AWARE

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

select * from t where id1=:i a3y1yrq36v0gn 2 2 3 Y Y

SQL> select sql_text, CHILD_NUMBER, IS_BIND_SENSITIVE, IS_BIND_AWARE, IS_SHAREABLE, executions from v$sql where sql_id='a3y1yrq36v0gn';

SQL_TEXT CHILD_NUMBER IS_BIND_SENSITIVE IS_BIND_AWARE IS_SHAREABLE EXECUTIONS

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

select * from t where id1=:i 0 Y N Y 2

select * from t where id1=:i 1 Y Y Y 1

這個時候，命中統計量出現變化。

SQL> select * from v$sql_cs_histogram where sql_id='a3y1yrq36v0gn';

ADDRESS HASH_VALUE SQL_ID CHILD_NUMBER BUCKET_ID COUNT

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

433F59CC 2255323636 a3y1yrq36v0gn 1 0 1

433F59CC 2255323636 a3y1yrq36v0gn 1 1 0

433F59CC 2255323636 a3y1yrq36v0gn 1 2 0

433F59CC 2255323636 a3y1yrq36v0gn 0 0 1

433F59CC 2255323636 a3y1yrq36v0gn 0 1 1

433F59CC 2255323636 a3y1yrq36v0gn 0 2 0

6 rows selected

SQL> select * from v$sql_cs_selectivity where sql_id='a3y1yrq36v0gn';

ADDRESS HASH_VALUE SQL_ID CHILD_NUMBER PREDICATE RANGE_ID LOW HIGH

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

433F59CC 2255323636 a3y1yrq36v0gn 1 =I 0 0.000241 0.000295

SQL> select * from v$sql_cs_statistics where sql_id='a3y1yrq36v0gn';

ADDRESS HASH_VALUE SQL_ID CHILD_NUMBER BIND_SET_HASH_VALUE PEEKED EXECUTIONS ROWS_PROCESSED BUFFER_GETS CPU_TIME

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

433F59CC 2255323636 a3y1yrq36v0gn 1 1852677911 Y 1 40 7 0

433F59CC 2255323636 a3y1yrq36v0gn 0 3804331937 Y 1 30000 2094 0

對新生成的遊標。Oracle將剛才的命中率放在第一個直方圖下。並且歸納該新遊標對應的統計量分佈數量。

此時，執行計劃為：

SQL> select * from table(dbms_xplan.display_cursor('a3y1yrq36v0gn',1,format => 'advanced'));

PLAN_TABLE_OUTPUT

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

SQL_ID a3y1yrq36v0gn, child number 1

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

select * from t where id1=:i

Plan hash value: 2247614985

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

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

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

| 1 | TABLE ACCESS BY INDEX ROWID| T | 11 | 66 | 2 (0)| 0

|* 2 | INDEX RANGE SCAN | IDX_T_ID1 | 11 | | 1 (0)| 0

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

Peeked Binds (identified by position):

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

1 - :I (VARCHAR2(30), CSID=873): 'U'

50 rows selected

ü 第四次執行繫結變數

此時，我們還是選擇’U’作為執行的繫結變數取值。

SQL> select * from t where id1=:i;

已選擇20行。

SQL> select sql_text, SQL_ID, VERSION_COUNT, VERSION_COUNT, executions, IS_BIND_SENSITIVE, IS_BIND_AWARE from v$sqlarea where sql_text like 'select * from t where id1=:i%';

SQL_TEXT SQL_ID VERSION_COUNT VERSION_COUNT EXECUTIONS IS_BIND_SENSITIVE IS_BIND_AWARE

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

select * from t where id1=:i a3y1yrq36v0gn 2 2 4 Y Y

SQL> col sql_text for a30;

SQL> select sql_text, SQL_ID, VERSION_COUNT,VERSION_COUNT,executions,IS_BIND_SENSITIVE, IS_BIND_AWARE from v$sqlarea where sql_text like 'select * from t where id1=:i%';

SQL_TEXT SQL_ID VERSION_COUNT VERSION_COUNT EXECUTIONS IS_BIND_SENSITIVE IS_BIND_AWARE

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

select * from t where id1=:i a3y1yrq36v0gn 2 2 4 Y Y

SQL> select sql_text, CHILD_NUMBER, IS_BIND_SENSITIVE, IS_BIND_AWARE, IS_SHAREABLE, executions from v$sql where sql_id='a3y1yrq36v0gn';

SQL_TEXT CHILD_NUMBER IS_BIND_SENSITIVE IS_BIND_AWARE IS_SHAREABLE EXECUTIONS

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

select * from t where id1=:i 0 Y N Y 2

select * from t where id1=:i 1 Y Y Y 2

SQL> select * from v$sql_cs_histogram where sql_id='a3y1yrq36v0gn';

ADDRESS HASH_VALUE SQL_ID CHILD_NUMBER BUCKET_ID COUNT

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

433F59CC 2255323636 a3y1yrq36v0gn 1 0 2

433F59CC 2255323636 a3y1yrq36v0gn 1 1 0

433F59CC 2255323636 a3y1yrq36v0gn 1 2 0

433F59CC 2255323636 a3y1yrq36v0gn 0 0 1

433F59CC 2255323636 a3y1yrq36v0gn 0 1 1

433F59CC 2255323636 a3y1yrq36v0gn 0 2 0

6 rows selected

相同命中子游標child_number=2。

ü 第五次繫結變數執行

當執行一次同分布資料值’Ｍ’的時候。

SQL> exec :i:='M';

PL/SQL 過程已成功完成。

已用時間: 00: 00: 00.02

SQL> select * from t where id1=:i;

已選擇20行。

此時，生成了一個新的遊標。進行索引計劃的呼叫。

SQL>

SQL_TEXT SQL_ID VERSION_COUNT VERSION_COUNT EXECUTIONS IS_BIND_SENSITIVE IS_BIND_AWARE

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

select * from t where id1=:i a3y1yrq36v0gn 3 3 5 Y Y

SQL_TEXT CHILD_NUMBER IS_BIND_SENSITIVE IS_BIND_AWARE IS_SHAREABLE EXECUTIONS

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

select * from t where id1=:i 0 Y N Y 2

select * from t where id1=:i 1 Y Y N 2

select * from t where id1=:i 2 Y Y Y 1

ADDRESS HASH_VALUE SQL_ID CHILD_NUMBER BUCKET_ID COUNT

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

433F59CC 2255323636 a3y1yrq36v0gn 2 0 1

433F59CC 2255323636 a3y1yrq36v0gn 2 1 0

433F59CC 2255323636 a3y1yrq36v0gn 2 2 0

433F59CC 2255323636 a3y1yrq36v0gn 1 0 2

433F59CC 2255323636 a3y1yrq36v0gn 1 1 0

433F59CC 2255323636 a3y1yrq36v0gn 1 2 0

433F59CC 2255323636 a3y1yrq36v0gn 0 0 1

433F59CC 2255323636 a3y1yrq36v0gn 0 1 1

433F59CC 2255323636 a3y1yrq36v0gn 0 2 0

9 rows selected

ADDRESS HASH_VALUE SQL_ID CHILD_NUMBER PREDICATE RANGE_ID LOW HIGH

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

433F59CC 2255323636 a3y1yrq36v0gn 2 =I 0 0.000241 0.001376

433F59CC 2255323636 a3y1yrq36v0gn 1 =I 0 0.000241 0.000295

ADDRESS HASH_VALUE SQL_ID CHILD_NUMBER BIND_SET_HASH_VALUE PEEKED EXECUTIONS ROWS_PROCESSED BUFFER_GETS CPU_TIME

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

433F59CC 2255323636 a3y1yrq36v0gn 2 1492060625 Y 1 40 7 0

433F59CC 2255323636 a3y1yrq36v0gn 1 1852677911 Y 1 40 7 0

433F59CC 2255323636 a3y1yrq36v0gn 0 3804331937 Y 1 30000 2094 0

同時子游標1也失去了被共享的能力。

3、ACS的優劣

透過上面的實驗，我們觀察到了基於直方圖和成本不斷試算的ACS工作過程。ACS的引入，帶來了下面幾個好處：

ü 緩解bind peeking帶來的效能問題

在oracle 11g之前，使用繫結變數的時候難免出現bind peeking副作用。也就是對於一些bad SQL遊標執行計劃過度共享，而且效能不穩定的現象。即使透過調整cursor_sharing，非繫結變數語句也會或者生成過多的子游標、或者同樣進入bind peeking過度共享。使用ACS後，可以自動的對bind peeking問題進行一系列的補救方式，以其生成適合的執行計劃配比。

ü 綜合化最優效能

使用ACS的最理想情況，就是Oracle對每一個使用繫結變數的父遊標生成多個適當的子游標執行計劃。當輸入不同的繫結變數值時，Oracle依據檢視的結果，“匹配”到合適的執行計劃中。這樣，既沒有消耗過多的library cache空間，又有匹配的SQl執行計劃對應。

同樣，世上沒有萬靈藥，任何事物都有其劣勢的一面。ACS也一樣。

ü 消耗更多的CPU和空間資源

進行ACS，Oracle意味著需要消耗更多的CPU進行成本配比試算和評估。同時，生成過多個執行計劃也意味著消耗記憶體library cache資源；

ü 自適應過程也會走彎路

自適應過程是一個事後實驗調節的過程。依據統計量和成本值，Oracle要出現若干次錯誤執行計劃的情況。

4、結論

作為oracle 11g推出的新特性，ACS一定程度上緩解了bind peeking，特別是生產環境下的，所帶來的問題。筆者認為，雖然不能強調是一個萬能的方案，但是一個試圖解決問題的有益嘗試。

Oracle自適應共享遊標——Adaptive Cursor Sharing（下）

相關文章