說說Oracle Bind Graduation（下）

 

上篇（http://space.itpub.net/17203031/viewspace-704144）中，我們解釋了與bind peeking等價的Oracle繫結變數技術bind graduation。本篇中，我們繼續深入探討該技術，和思考在bind graduation的環境下我們應用開發應該注意些什麼。

 

1、 宣告值還是實際值？

 

上面的實驗，告訴我們在執行PL/SQL繫結變數時，會出現繫結變數後設資料metadata分割槽的情況。那麼，究竟是我們宣告variable的變數引發的graduation，還是我們對變數賦值引發的graduation。我們就用上面發現的32 bytes分割槽作為實驗物件。

 

 

SQL> declare

  2    n number(10);

  3    v varchar2(33);

  4  begin

  5    n := 14;

  6    v := 'Tom Kyte'; //v值顯然不足32bytes；

  7 

  8    execute immediate 'insert /*+ bind_data_plsql */into t values (:x, :y)'

  9                      using n, v;

 10    commit;

 11 

 12    v := lpad('Tom Kyte',33,'_');//達到33 bytes；

 13 

 14    execute immediate 'insert /*+ bind_data_plsql */into t values (:x, :y)'

 15                      using n, v;

 16    commit;

 17  end;

 18  /

 

PL/SQL procedure successfully completed

 

 

此時，我們觀察一下游標使用的情況。判斷在相同的宣告variable的情況下，遊標是否發生共享。注意：本實驗是在上面實驗的基礎上進行。

 

 

 

SQL> select sql_text, sql_id, address, version_count, EXECUTIONS from  v$sqlarea where sql_text like 'insert /*+ bind_data_plsql */%';

 

SQL_TEXT                                            SQL_ID        ADDRESS  VERSION_COUNT EXECUTIONS

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

insert /*+ bind_data_plsql */into t values (:x, :y) a765qwfk986aj 4D74ECA8             2          4

 

SQL> select sql_id, child_number, address, child_address,executions  from v$sql where sql_id='a765qwfk986aj';

 

SQL_ID        CHILD_NUMBER ADDRESS  CHILD_ADDRESS EXECUTIONS

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

a765qwfk986aj            0 4D74ECA8 4D74EA24               1

a765qwfk986aj            1 4D74ECA8 49D19D5C               3

 

 

增加的兩次執行，全部落在了child_number=1的子游標上。我們再次觀察該子游標對應的繫結變數後設資料。

 

 

SQL> select * from v$sql_bind_metadata where address='49D19D5C';

 

ADDRESS    POSITION   DATATYPE MAX_LENGTH  ARRAY_LEN BIND_NAME

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

49D19D5C          2          1        128          0 Y

49D19D5C          1          2         22          0 X

 

 

顯然，超過了32bytes的繫結變數定義，都是落在了128上限的後設資料metadata分割槽上。自然也就共享child_number=1的子游標。我們實驗的目的也就達到了，Oracle在進行bind graduation的時候，使用的是PL/SQL繫結變數的宣告型別長度。

 

 

2、 上限

 

上面的一系列實驗，讓我們看到了對字元繫結變數，在執行PL/SQL程式碼時存在[0-32]，[33-128]至少兩個分割槽。那麼，varchar2的長度上限是4000。這個範圍內，還存在什麼分割槽呢？我們透過實驗來確定。

 

 

SQL> declare

  2    n number(10);

  3    v varchar2(500); //增加到500長度；

  4  begin

  5    n := 14;

  6    v := 'Tom Kyte';

  7 

  8    execute immediate 'insert /*+ bind_data_plsql */into t values (:x, :y)'

  9                      using n, v;

 10    commit;

 11  end;

 12  /

 

PL/SQL procedure successfully completed

 

 

當我們定義增加到500，實驗遊標情況。

 

 

SQL> select sql_id, child_number, address, child_address, executions  from v$sql where sql_id='a765qwfk986aj';

 

SQL_ID        CHILD_NUMBER ADDRESS  CHILD_ADDRESS  EXECUTIONS

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

a765qwfk986aj            0 4D74ECA8 4D74EA24               1

a765qwfk986aj            1 4D74ECA8 49D19D5C               3

a765qwfk986aj            2 4D74ECA8 49D06E6C               2（實驗中，上面程式碼執行兩次）

 

 

SQL> select * from v$sql_bind_metadata where address='49D06E6C';

 

ADDRESS    POSITION   DATATYPE MAX_LENGTH  ARRAY_LEN BIND_NAME

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

49D06E6C          2          1       2000          0 Y

49D06E6C          1          2         22          0 X

 

 

在128 bytes之上，我們發現了第三個分割槽上限2000。也就是[128-2000]以內，是一個graduation範圍。那麼，在提高繫結變數宣告長度，能否探查到新的分割槽了呢？

 

 

SQL> declare

  2    n number(10);

  3    v varchar2(2500);

  4  begin

  5    n := 14;

6    v := 'Tom Kyte';

（篇幅原因，有省略……）

 

 

觀察執行child cursor情況。

 

 

SQL> select sql_id, child_number, address, child_address,executions  from v$sql where sql_id='a765qwfk986aj';

 

SQL_ID        CHILD_NUMBER ADDRESS  CHILD_ADDRESS EXECUTIONS

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

a765qwfk986aj            0 4D74ECA8 4D74EA24               1

a765qwfk986aj            1 4D74ECA8 49D19D5C               3

a765qwfk986aj            2 4D74ECA8 49D06E6C               3

 

 

依然共享2000上限遊標。注意：雖然遊標child_number=2的max length列標註的是2000，我們宣告長度2500的時候，依然實現了共享。

 

 

SQL> declare

  2    n number(10);

  3    v varchar2(3000);

  4  begin

  5    n := 14;

  6    v := 'Tom Kyte';

 

 

實現使用3000的時候，依然共享了上限2000的child cursor。

 

 

SQL> select sql_id, child_number, address, child_address, executions  from v$sql where sql_id='a765qwfk986aj';

 

SQL_ID        CHILD_NUMBER ADDRESS  CHILD_ADDRESS  EXECUTIONS

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

a765qwfk986aj            0 4D74ECA8 4D74EA24               1

a765qwfk986aj            1 4D74ECA8 49D19D5C               3

a765qwfk986aj            2 4D74ECA8 49D06E6C               4

 

 

 

經過上面的實驗，我們發現了bind graduation在變數長度變化時的一些特性。在128bytes之上，還存在一個有效的2000作為遊標上限。突破2000之後，繫結變數的metadata資料上限始終顯示2000。之後，即使我們宣告變數長度超過2000，也是會共享這個2000的子游標。說明，當字元型別繫結變數長度宣告超過了2000之後，bind graduation機制失去了效果。所有變數共享一個後設資料cursor。

 

 

5、結論與思考

 

經過上面的實驗，我們雖然沒有證明《Troubleshooting》作者實驗的SQL下bind graduation現象。但是起碼證明了在Oracle 11gR2的情況下，PL/SQL使用字元繫結變數時，還是存在bind graduation現象的。筆者在思考一個問題，作為與bind peeking相同定位的bind graduation，Oracle推出它的意義何在呢？

 

經過和其他同事的討論，認為可能原因有如下兩個：

 

ü        bind peeking緩解，提供多次peeking機會

 

從效果來看，Oracle bind graduation會增加子游標的數量。如果單就bind peeking而言，在Oracle 11g的ACS（Adaptive Cursor Sharing）出現之前，Oracle繫結變數使用的子游標數量是很少的。

 

Bind graduation出現之後，我們最直觀的感覺是child cursor增多，對應的執行計劃增多。原有的可能只用一個執行計劃可以覆蓋的繫結變數語句，可能要有多個執行計劃才能覆蓋。

 

對繫結變數語句而言，每次生成子游標，就意味著要進行一次hard parse，就意味著要進行一次peeking。生成與Peeking value對應的執行計劃。PL/SQL程式碼中對變數宣告長度的不一致，直接意味著不同的程式模組和功能模組。Oracle也許認為這樣出現bind peeking問題的機率較高。於是取巧採用變數宣告的方式進行區分管理。同時，劃分割槽域又不是很多，從而限制了子游標出現的數量。多次peeking，形成多個子遊標，配對更合適的執行計劃。

 

ü        繫結變數儲存

 

對執行計劃而言，Oracle是需要單獨分配記憶體空間給執行計劃進行儲存的。如果其中有使用繫結變數，Oracle是會將繫結變數儲存在child cursor中的。在分配varchar2型別的繫結變數大小空間時，使用bind graduation可以分配略小的適當空間。

 

 

最後，我們聊聊bind graduation的實際意義，主要是針對pl/sql程式而言的。雖然會存在bind graduation現象，但是我們說實現graduation的分割槽數量是有限的。也就是說，即使多次生成child cursor，帶來version count過多的風險也是有限的。

 

如果要是很極端的情況，比如專案組希望實現絕對的共享或者說變數數目較多引起version count過多，可以使用10503事件控制bind graduation的出現，或者直接在程式碼中宣告varchar2(2000)的繫結變數即可。