繫結變數是我們目前開發系統應用中廣泛使用的資料庫SQL語言技術。大多數情況下，我們藉助繫結變數可以有效的提高shared cursor使用的效率，減少SQL硬解析的出現。

在Oracle經典著作《Troubleshooting Oracle Performance》（簡稱《Troubleshooting》）中，Christian Antognini介紹了兩種使用繫結變數帶來的問題，一個就是“大名鼎鼎”的bind peeking，另一個就是bind graduation.

Bind peeking因其在執行“Bad”SQL時，存在強制共享非最優執行計劃的可能被廣泛關注。而bind graduation無論在網路還是MOS中，都是較少涉及的話題。本篇就介紹一下Oracle bind graduation技術細節。

1、 Bind Graduation簡介

Bind Graduation技術主要是針對字串繫結變數的一種SQL共享技術。在我們使用SQL繫結變數時，除了字面SQL和其他一些環境變數因素會影響到SQL子游標共享外，使用繫結變數的長度也是會影響到SQL cursor sharing的。

根據《Troubleshooting》中的介紹，Oracle對輸入繫結變數字串進行長度分割槽。小於32 btyes長度的為一個劃分割槽，32 bytes到128 bytes為第二區。其他的上限分別為1000和2000。當輸入的繫結變數字串定義var在不同區內的時候，是不會發生子游標共享的。

Bind Graduation技術的推出，初衷還是為了加強共享遊標的可用性和一定程度上提高執行計劃最優性。實際情況是如何呢？我們下面分別進行試驗驗證。

2、 純SQL語句下的graduation

下面實驗使用《Troubleshooting》的案例，在Oracle 11gR2下進行。

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

TNS for Linux: Version 11.2.0.1.0 - Production

NLSRTL Version 11.2.0.1.0 - Production

SQL> create table t (n number(10), v varchar2(100));

Table created

進行繫結變數插入。

SQL> var n number;

SQL> var v varchar2(32); //定義了一個32位的字串；

SQL> exec :n:=1; :v:='Helicon';

PL/SQL procedure successfully completed

n

---------

1

v

---------

Helicon

//第一次執行語句；

SQL> insert /*+ bind_date_length */ into t (n,v) values (:n, :v);

1 row inserted

n

---------

1

v

---------

Helicon

SQL> commit;

Commit complete

此時，我們檢查遊標情況。

//父遊標情況；

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

SQL_TEXT SQL_ID ADDRESS VERSION_COUNT EXECUTIONS

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

insert /*+ bind_date_length */ into t (n,v) values (:n, :v) 5j0dt5yvaj59r 3F522248 1 1

//子游標情況；

SQL> select sql_id, child_number, address, child_address, sqltype from v$sql where sql_id='5j0dt5yvaj59r';

SQL_ID CHILD_NUMBER ADDRESS CHILD_ADDRESS SQLTYPE

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

5j0dt5yvaj59r 0 3F522248 475047B8 6

上面的結果中，我們發現執行語句生成了一對父子游標。此時，我們需要引入檢視v$sql_bind_metadata，表示某個特定子游標的某個繫結變數後設資料資訊。使用子游標v$sql的child_address進行連線定位。

SQL> select * from v$sql_bind_metadata where address='475047B8';

ADDRESS POSITION DATATYPE MAX_LENGTH ARRAY_LEN BIND_NAME

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

475047B8 2 1 4000 0 V

475047B8 1 2 22 0 N

datatype中，1表示varchar2，而2表示number。注意這裡和書中實驗結果不同，字串繫結變數直接最大值為4000，是varchar2的極限大小。根據繫結變數遊標記憶體儲存機制，意味著Oracle會分配最大（但不一定立即分配）4000bytes大小的空間儲存繫結變數。這樣，也就意味著繫結變數後設資料存在共享的空間。

下面，我們重新定義繫結變數。

SQL> var v varchar2(33);

SQL> exec :n:=4; :v:='Terminus';

PL/SQL procedure successfully completed

n

---------

4

v

---------

Terminus

SQL> insert /*+ bind_date_length */ into t (n,v) values (:n, :v);

1 row inserted

n

---------

4

v

---------

Terminus

SQL> commit;

Commit complete

我們越界定義了v值，之後檢視繫結變數的情況。

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

SQL_TEXT SQL_ID ADDRESS VERSION_COUNT EXECUTIONS

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

insert /*+ bind_date_length */ into t (n,v) values (:n, :v) 5j0dt5yvaj59r 3F522248 1 2

SQL> select * from v$sql_bind_metadata where address='475047B8';

ADDRESS POSITION DATATYPE MAX_LENGTH ARRAY_LEN BIND_NAME

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

475047B8 2 1 4000 0 V

475047B8 1 2 22 0 N

注意：此時發生了SQL遊標共享。相同的父遊標子游標執行了兩次，沒有生成新的子游標。意味著發生了遊標共享。子游標繫結變數後設資料的max_length沒有發生變化。

至此，書中的Demo演示實驗在我們的環境中是不起作用的。在Oracle 11gR2環境下，直接執行SQL語句使用繫結變數，時不會發生bind graduation現象的。

那麼怎麼樣才能激發出bind graduation呢？我們使用pl/sql程式實驗。

3、PL/SQL下的bind graduation

純SQL下是不會出現bind graduation的，那麼使用PL/SQL程式中使用繫結變數，是否有這樣的現象呢？

首先執行下面匿名PL/SQL塊。

declare

n number(10);

v varchar2(32); //第一次宣告varchar2(32)

begin

n := 14;

v := 'Tom Kyte';

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

using n, v;

commit;

end;

/

之後，檢查父子游標情況如下：

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 1 1

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

SQL_ID CHILD_NUMBER ADDRESS CHILD_ADDRESS SQLTYPE

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

a765qwfk986aj 0 4D74ECA8 4D74EA24 6

使用子游標地址child_address，獲取本次子游標繫結變數的後設資料資訊。

SQL> select * from v$sql_bind_metadata where address='4D74EA24';

ADDRESS POSITION DATATYPE MAX_LENGTH ARRAY_LEN BIND_NAME

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

4D74EA24 2 1 32 0 Y

4D74EA24 1 2 22 0 X

注意，Max_length的取值不再是4000，而是傳說中的32 bytes！似乎在PL/SQL下，bind graduation是存在的。

形成了一對父子游標，之後改變變數宣告長度，進行試驗。

SQL> declare

2 n number(10);

3 v varchar2(100); //定義一個超過128的變數。

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

此時父子游標情況如下：

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 2

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

SQL_ID CHILD_NUMBER ADDRESS CHILD_ADDRESS SQLTYPE

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

a765qwfk986aj 0 4D74ECA8 4D74EA24 6

a765qwfk986aj 1 4D74ECA8 49D19D5C 6

相同的SQL語句字面和環境資訊，只是宣告的繫結變數長度差異。引起新子游標的生成。

SQL> select * from v$sql_bind_metadata where address='4D74EA24';

ADDRESS POSITION DATATYPE MAX_LENGTH ARRAY_LEN BIND_NAME

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

4D74EA24 2 1 32 0 Y

4D74EA24 1 2 22 0 X

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

我們使用子游標child_address檢查繫結變數後設資料資訊，發現了傳說中的128。也就是說，當PL/SQL執行的時候，Oracle不會將繫結變數如SQL執行時，直接判定繫結變數（字元型）為最大4000bytes，而是分割槽分別進行。

如果宣告的繫結變數varchar2為32bytes以下，那麼Oracle對bind variable的定義就是max_length為32。這樣其他所有生成32bytes繫結變數的SQL起碼在繫結變數後設資料（bind variable metadata）的層面上，是可以共享的。

如果超過了32位，就進行128bytes上限的範圍，保留繫結變數大小的空間就是128bytes了。繫結變數後設資料發生了變化，自然也就不可能進行cursor sharing，子游標共享了。於是Oracle必然重新生成一個child cursor對應。

上面的實驗，告訴了我們bind graduation的幾個特性：

1、在純SQL中不存在使用bind graduation的情況，而是在pl/sql中使用；

2、0-32、32-128分別為兩個bind分割槽，不同分割槽的繫結變數宣告值不共享遊標；

在MOS[ID 258742.1]中，找到一段關於graduation的隻言片語描述：

Depending on the length used, the character binds in the child cursor can all be created using the same bind length;

skipping bind graduation and keeping the child chain relatively small.

This helps to alleviate a potential cursor-sharing problem related to graduated binds.

The level of the event is the bind length to use, in bytes.

從上面的描述中，我們可以看到graduation的技術初衷還是為了緩解（而不是解決）繫結變數帶來的遊標共享問題。

下面，我們還有一些疑問：除了128下面的分割槽，還有沒有其他的分割槽片段？超過分割槽限度的SQL語句如何進行共享？我們在下篇中繼續討論。

說說Oracle Bind Graduation（上）

相關文章