Oracle的繫結變數技術，是解決記憶體SQL語句硬解析過多，防止出現資源爭用的重要手段。但是，世上沒有萬靈藥。繫結變數在引入cursor sharing與增加軟解析的同時，也帶來CBO環境下的bind peeking問題。從Oracle 11g開始，Oracle Database引入了Adaptive Cursor Sharing新特性，將繫結變數執行計劃確定變化為一個基於統計量分析的自適應過程。

1、 從Oracle 10g下的bind peeking談起

Bind peeking問題的根源在於CBO時代SQL執行計劃的生成機制。CBO生成執行計劃與RBO存在很大差異，CBO是依據資料分佈的統計量資訊，生成成本最優的執行計劃。但是，使用繫結變數的SQL語句在這個環節似乎存在一些問題。如下一條SQL語句。

select * from t where wner=:x

owner列上存在索引。那麼輸入上面的一句SQL給最佳化器，Oracle生成何種型別的執行計劃呢？走索引還是全表掃描？

答案是都有可能。owner的分佈如果是非平均，存在偏移。那麼不同的:x取值，生成的最優執行計劃必然不同。這個時候，Oracle會在第一次解析的時候，“偷偷”檢視peek一下輸入的繫結變數值。根據peek到的資料值來確定執行計劃，儲存在liberary cache中，作為下一次執行的cursor sharing。如果下次輸入的:X變數恰好是和第一次取值分佈差異很大的資料值，那麼執行計劃就是有問題的，甚至就是錯誤的。

下面我們簡單實驗一下Oracel 10g上的bind peeking。首先還是準備環境：

SQL> select * from v$version;

BANNER

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

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod

SQL> create table t (id1 varchar2(10), id2 varchar2(10), id3 varchar2(10));

Table created

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

ID1 COUNT(*)

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

K 10000

M 20

A 10000

在id1列上存在索引，而且id1列取值分佈及其不平均。

SQL> create index idx_t_id1 on t(id1);

Index created

SQL> exec dbms_stats.gather_table_stats(user,'T',cascade => true);

PL/SQL procedure successfully completed

下面我們先使用非繫結變數，來精確獲取應有的執行計劃情況。由於篇幅原因直接列出結果。

SQL語句結構	執行關鍵路徑	資料行數
select * from t where id1='K';	全表掃描	10000
select * from t where id1='M';	索引路徑	20
select * from t where id1='A';	全表掃描	10000

由於資料值取值偏移的原因，id1取定’M’時執行索引路徑，而取定’K’時執行的是全表掃描。下面觀察使用繫結變數的情況：

//定義繫結變數取值；

SQL> var i varchar2(10);

SQL> exec :i := 'K';

PL/SQL 過程已成功完成。

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

已選擇10000行。

此時，我們檢查liberary cache中快取的執行計劃。

//父遊標情況；

SQL> select sql_text, SQL_ID, VERSION_COUNT,BIND_DATA from v$sqlarea where sql_text like 'select * from t%';

SQL_TEXT SQL_ID VERSION_COUNT

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

select * from t where id1=:i a3y1yrq36v0gn 1

Executed in 0.094 seconds

//子游標中只有一個對應，也就是當前只有一個執行計劃；

SQL> select PLAN_HASH_VALUE, CHILD_NUMBER from v$sql where sql_id='a3y1yrq36v0gn';

PLAN_HASH_VALUE CHILD_NUMBER

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

1601196873 0

Executed in 0.047 seconds

使用sql_id和chile_number抽取出可讀的執行計劃，如下：

//抽取出執行計劃；

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

PLAN_TABLE_OUTPUT

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

SQL_ID a3y1yrq36v0gn, child number 0

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

select * from t where id1=:i

Plan hash value: 1601196873

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

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

| 0 | SELECT STATEMENT | | | | 9 (100)| |

|* 1 | TABLE ACCESS FULL| T | 10000 | 60000 | 9 (12)| 00:00:01 |

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

Peeked Binds (identified by position):

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

1 - :I (VARCHAR2(30), CSID=852): 'K'

Predicate Information (identified by operation id):

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

1 - filter("ID1"=:I)

注意上面的執行計劃，採用了全表掃描的正確計劃。同時，也告訴我們採用的peeking值為’K’。此時，我們變換繫結變數值。

SQL> exec :i:='M';

PL/SQL 過程已成功完成。

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

已選擇20行。

此時，我們再次觀察父子游標狀態。

SQL> select PLAN_HASH_VALUE, CHILD_NUMBER from v$sql where sql_id='a3y1yrq36v0gn';

PLAN_HASH_VALUE CHILD_NUMBER

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

1601196873 0

Executed in 0.031 seconds

注意！第二次執行的SQL語句和第一次完全相同，也就意味著父遊標相同。繫結變數取值有所差別。但是我們之後觀察子游標，沒有出現新的子游標出現。說明第二次執行的還是原來的共享遊標，也就是全表掃描執行計劃。這個在變數取定’M’的情況下，完全是錯誤的！！

上面的實驗告訴我們：在使用繫結變數的時候，雖然可以提高遊標共享機率，減少硬解析的出現。但是由於bind peeking問題，使所有繫結變數採用相同的執行計劃。這個顯然是有問題的，在實際生產環境下會出現效能的無故遞減問題。

Oracle 11g之前，bind peeking問題一直是一個很困擾的障礙。很多時候甚至迫使運維人員暫時性的終止bind peeking的使用。於是Oracle在11g開始，嘗試使用一種自適應的遊標共享方式（Adaptive Cursor Sharing），來提高繫結變數情況下的正確執行計劃生成機率。

2、Adaptive Cursor Sharing初探

首先我們進行一系列的實驗，給出直觀的ACS（Adaptive Cursor Sharing）效果。作為對比，我們採用的資料構成環境與上面相同，差別就是在oracle11g版本上。

SQL> select * from v$version;

BANNER

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

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

在使用繫結變數的時候，我們逐步觀測。

SQL> var i varchar2(10);

SQL> exec :i:='M';

PL/SQL 過程已成功完成。

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

已選擇20行。

此時，我們觀察父子游標情況。

//父遊標

SQL> select sql_text, sql_id, 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 EXECUTIONS IS_BIND_SENSITIVE IS_BIND_AWARE

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

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

Executed in 0.14 seconds

//子游標

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

SQL_ID CHILD_NUMBER IS_BIND_SENSITIVE IS_BIND_AWARE IS_SHAREABLE

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

a3y1yrq36v0gn 0 Y N Y

Executed in 0.046 seconds

由於第一次呼叫，執行後只生成了一個子遊標物件。注意，從11g開始，在v$sql中加入了三個檢視列為is_bind_sensitive、is_bind_aware和is_shareable。

ü is_bind_sensitive表示該子游標中是否使用了繫結變數要素，採用bind peeking方法進行執行計劃生成；

ü is_bind_aware表示該子游標是否使用了extended cursor sharing技術，也就是ACE；

ü is_shareable表示該子游標可否被下次軟解析所共享使用。如果設定為N，就表示該子游標失去了共享價值，等待被Age Out出記憶體；

當前我們只有一次bind peeking呼叫，沒有其他的可能。所以is_bind_senstive為Y，is_shareable為Y，同時is_bind_aware未啟動。該執行計劃為：

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

PLAN_TABLE_OUTPUT

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

SQL_ID a3y1yrq36v0gn, child number 0

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

select * from t where id1=:i

Plan hash value: 2247614985

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

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

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

| 1 | TABLE ACCESS BY INDEX ROWID| T | 20 | 120 | 2 (0)| 0

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

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

Peeked Binds (identified by position):

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

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

Predicate Information (identified by operation id):

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

2 - access("ID1"=:I)

Executed in 1.154 seconds

此時，啟用bind peeking，生成索引執行計劃。接下來進行第二次呼叫。

//選擇了應該進行全表掃描的K值；

SQL> exec :i:='K';

PL/SQL 過程已成功完成。

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

已選擇10000行。

檢視父遊標情況。

SQL> select sql_text, sql_id, 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 EXECUTIONS IS_BIND_SENSITIVE IS_BIND_AWARE

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

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

Executed in 0.125 seconds

從父遊標的情況看，當前還是隻有一個子遊標，呈現bind peeking老現象。我們再次呼叫的時候，出現了不同。

SQL> exec :i:='K';

PL/SQL 過程已成功完成。

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

已選擇10000行。

SQL> select sql_text, sql_id, 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 EXECUTIONS IS_BIND_SENSITIVE IS_BIND_AWARE

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

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

Executed in 0.125 seconds

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

SQL_ID CHILD_NUMBER IS_BIND_SENSITIVE IS_BIND_AWARE IS_SHAREABLE

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

a3y1yrq36v0gn 0 Y N Y

a3y1yrq36v0gn 1 Y Y Y

Executed in 0.031 seconds

注意，在第二次使用’K’的時候，Oracle沒有選擇第一條錯誤的執行子游標，而是產生出一個新的遊標（child_number=1）。而且，這個新生成的遊標具有可以共享和extended cursor sharing特性。

那麼，我們抽取出第二條執行計劃檢視。

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: 1601196873

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

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

| 0 | SELECT STATEMENT | | | | 10 (100)| |

|* 1 | TABLE ACCESS FULL| T | 10000 | 60000 | 10 (0)| 00:00:01 |

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

Peeked Binds (identified by position):

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

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

Predicate Information (identified by operation id):

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

1 - filter("ID1"=:I)

48 rows selected

Executed in 0.421 seconds

此時，終於生成了一個全新的全表掃描執行計劃，使用的bind peeking值為’K’。如果我們在使用新的繫結變數值，如何呢？

//使用應該進行全表掃描的資料值；

SQL> exec :i:='A';

PL/SQL 過程已成功完成。

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

已選擇10000行。

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

SQL_ID CHILD_NUMBER IS_BIND_SENSITIVE IS_BIND_AWARE IS_SHAREABLE EXECUTIONS

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

a3y1yrq36v0gn 0 Y N Y 2

a3y1yrq36v0gn 1 Y Y Y 2

Executed in 0.046 seconds

注意子游標0的執行次數變化，可以猜出來剛才的那次’A’是使用子游標0，索引路徑執行計劃。應該說是錯誤和不合適的。

接著進行測試：

SQL> exec :i:='L';

PL/SQL 過程已成功完成。

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

未選定行

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

SQL_ID CHILD_NUMBER IS_BIND_SENSITIVE IS_BIND_AWARE IS_SHAREABLE EXECUTIONS LAST_LOAD_TIME

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

a3y1yrq36v0gn 0 Y N N 2 2011-07-27/16:32:21

a3y1yrq36v0gn 1 Y Y Y 2 2011-07-27/16:45:07

a3y1yrq36v0gn 2 Y Y Y 1 2011-07-27/16:52:10

Executed in 0.063 seconds

採用了新值，Oracle再次生成了一個新遊標。同時child_number為0的子游標is_shareable欄位為N，表示退出共享週期。剩下的兩個子游標為可以進行共享的extended cursor sharing子游標。新生成遊標執行計劃如下：

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

PLAN_TABLE_OUTPUT

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

SQL_ID a3y1yrq36v0gn, child number 2

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

select * from t where id1=:i

Plan hash value: 2247614985

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

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

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

| 1 | TABLE ACCESS BY INDEX ROWID| T | 10 | 60 | 2 (0)| 0

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

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

Peeked Binds (identified by position):

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

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

Predicate Information (identified by operation id):

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

2 - access("ID1"=:I)

50 rows selected

Executed in 0.453 seconds

新生成的子游標使用索引路徑，使用了繫結變數‘L’生成出的。應該說是一個正確的執行計劃。

在oracle 11g下，我們一共執行了5次目標繫結變數語句，生成了3個子遊標物件，其中2個可以進行extended cursor sharing。5次中有2次是執行了錯誤的執行計劃，以後這個比例隨著執行正確率不斷增加，因為目標子游標執行計劃已經全部生成。

3、ACE特點總結

經過上面的實驗，我們可以初步歸納出ACE的特點：

ü 與單純的bind peeking（Oracle 11g以前）相比，ACE不會將第一次bind peeking的結果作為唯一的備選執行計劃進行執行，會進行多次peeking；

ü ACE不是萬能的，也會使用錯誤的遊標執行計劃；

ü ACE的本質是不斷多次的peeking，經過統計分析不斷調整實現的執行計劃。相同的兩次輸入，也許結果執行計劃就有不同；

ü ACE是一個自適應過程，內部透過一系列的成本計算，不同作出共享遊標或者生成新一次peeking的決策。應該說是Oracle 11g的一個新特性，一定程度上向解決bind peeking副作用作出有益的嘗試；

本篇中我們觀察了ACE的現象特點和與傳統Bind peeking區別。下面系列中，我們將繼續關注ACE。從它相關的系統引數和影響檢視，一起探究ACE的本質。

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

相關文章