---

---

繫結變數分級（Bind Graduation）是指Oracle在PL/SQL程式碼中會根據文字型繫結變數的定義長度而將這些文字型繫結變數分為四個等級，如下所示：

l 定義長度小於等於32位元組（Byte）的文字型繫結變數被分在第一個等級，Oracle為其分配32位元組的記憶體空間。

l 定義長度在[33,128]位元組之間的被分在第二個等級，Oracle為其分配128位元組的記憶體空間。

l 定義長度在[129,2000]位元組之間的文字型繫結變數被分在第三個等級，Oracle為其分配2000位元組的記憶體空間。

l 定義長度在2000位元組以上被分在第四個等級，Oracle為此等級的文字型繫結變數分配的記憶體空間大小取決於對應文字型繫結變數所傳入的實際繫結變數值的大小。如果實際傳入的繫結變數值小於或等於2000位元組，那麼Oracle會為其分配2000位元組的記憶體空間。如果實際傳入的繫結變數值大於2000位元組，那麼Oracle會為其分配4000位元組的記憶體空間。

需要注意的是，繫結變數分級僅適用於文字型的繫結變數，Oracle不會對數值（NUMBER）型的繫結變數做繫結變數分級。Oracle資料庫中數值型的變數最大隻能佔用22位元組，所以對於數值型的繫結變數而言，Oracle統一為其分配了22位元組的記憶體空間。

如果在PL/SQL程式碼中使用了文字型繫結變數，只要其SQL文字中文字型繫結變數的定義長度發生了變化，那麼Oracle為這些繫結變數所分配的記憶體空間的大小也可能會隨之發生變化，而一旦Oracle為這些繫結變數所分配的記憶體空間的大小發生了變化，那麼該SQL之前儲存在Child Cursor中的解析樹和執行計劃就不能被重用了。其原因是Child Cursor中除了會儲存目標SQL的解析樹和執行計劃之外，還會儲存該SQL所使用的繫結變數的型別和長度，這意味著即使該SQL的SQL文字沒有發生任何改變，只要其SQL文字中文字型繫結變數的定義長度發生了變化，那麼該SQL再次執行時就可能還是做硬解析（新生成一個子遊標）。

下面給出一個示例（資料庫版本為11.2.0.3）：

建表T_BG_20170610_LHR，並給出5個PL/SQL程式碼：

CREATE TABLE T_BG_20170610_LHR(N NUMBER(10),V VARCHAR2(3000));

--SQL_TEXT1:硬解析

DECLARE

  N NUMBER(10) :=1;--分配22位元組的記憶體空間

  V VARCHAR2(32) :='XIAOMAIMIAO1';--分配32位元組的記憶體空間

BEGIN

  EXECUTE IMMEDIATE 'INSERT INTO T_BG_20170610_LHR VALUES(:N,:V)' USING N, V;

  COMMIT;

END;

/

--SQL_TEXT2:硬解析

DECLARE

  N NUMBER(10) :=2;--分配22位元組的記憶體空間

  V VARCHAR2(33) :='XIAOMAIMIAO2';--分配128位元組的記憶體空間

BEGIN

  EXECUTE IMMEDIATE 'INSERT INTO T_BG_20170610_LHR VALUES(:N,:V)' USING N, V;

  COMMIT;

END;

/

 

--SQL_TEXT3:硬解析

DECLARE

  N NUMBER(10) :=3;--分配22位元組的記憶體空間

  V VARCHAR2(129) :='XIAOMAIMIAO3';--分配2000位元組的記憶體空間

BEGIN

  EXECUTE IMMEDIATE 'INSERT INTO T_BG_20170610_LHR VALUES(:N,:V)' USING N, V;

  COMMIT;

END;

/

 

--SQL_TEXT4:軟解析

DECLARE

  N NUMBER(10) :=4;--分配22位元組的記憶體空間

  V VARCHAR2(2001) :='XIAOMAIMIAO4';--分配2000位元組的記憶體空間

BEGIN

  EXECUTE IMMEDIATE 'INSERT INTO T_BG_20170610_LHR VALUES(:N,:V)' USING N, V;

  COMMIT;

END;

/

--SQL_TEXT5:軟解析

DECLARE

  N NUMBER(10) :=5;--分配22位元組的記憶體空間

  V VARCHAR2(32767) :='XIAOMAIMIAO5';--分配2000位元組的記憶體空間

BEGIN

  EXECUTE IMMEDIATE 'INSERT INTO T_BG_20170610_LHR VALUES(:N,:V)' USING N, V;

  COMMIT;

END;

/

--SQL_TEXT6: 硬解析

DECLARE

  N NUMBER(10) :=6;  --分配22位元組的記憶體空間

  V VARCHAR2(32767) :=RPAD('XIAOMAIMIAO6',2002,'8');  --字串長度為2002，分配4000位元組的記憶體空間

BEGIN

  EXECUTE IMMEDIATE 'INSERT INTO T_BG_20170610_LHR VALUES(:N,:V)' USING N, V;

  COMMIT;

END;

/

執行上述建表語句和PL/SQL程式碼，檢視結果：

LHR@orclasm > col v format a13

LHR@orclasm > select * from T_BG_20170610_LHR T WHERE T.N<=5;

 

         N V

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

         1 XIAOMAIMIAO1

         2 XIAOMAIMIAO2

         3 XIAOMAIMIAO3

         4 XIAOMAIMIAO4

         5 XIAOMAIMIAO5

LHR@orclasm > SELECT T.N,LENGTH(T.V) FROM T_BG_20170610_LHR T;

 

         N LENGTH(T.V)

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

         1          12

         2          12

         3          12

         4          12

         5          12

         6        2002

一旦Oracle為這些文字型繫結變數所分配的記憶體空間的大小發生了變化，那麼該SQL之前儲存在Child Cursor中的解析樹和執行計劃就不能被重用了。所以這裡Oracle在執行範例PL/SQL程式碼1、2、3時每次都是硬解析，但在執行範例PL/SQL程式碼4和5時會用軟解析/軟軟解析，因為範例PL/SQL程式碼4和5可以重用之前執行的範例PL/SQL程式碼3中目標SQL（即INSERT INTO T_BG_20170610_LHR VALUES(:N,:V)）的解析樹和執行計劃。在執行範例PL/SQL程式碼6時是硬解析，這意味著對於此目標SQL而言，其所在的Parent cursor下會有4個Child Cursor：

LHR@orclasm > col sql_text format a60

LHR@orclasm > SELECT SQL_TEXT,SQL_ID,VERSION_COUNT,EXECUTIONS FROM V$SQLAREA WHERE SQL_TEXT LIKE 'INSERT INTO T_BG_20170610_LHR VALUES%';

 

SQL_TEXT                                                     SQL_ID        VERSION_COUNT EXECUTIONS

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

INSERT INTO T_BG_20170610_LHR VALUES(:N,:V)                  01g03pruhphqc             4          6

 

 

LHR@orclasm > SELECT SQL_TEXT,SQL_ID,D.CHILD_NUMBER,D.CHILD_ADDRESS,EXECUTIONS FROM V$SQL D WHERE SQL_ID = '01g03pruhphqc';

 

SQL_TEXT                                                     SQL_ID        CHILD_NUMBER CHILD_ADDRESS    EXECUTIONS

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

INSERT INTO T_BG_20170610_LHR VALUES(:N,:V)                  01g03pruhphqc            0 00000000AA902CE8          1    <<----對應PL/SQL程式碼1

INSERT INTO T_BG_20170610_LHR VALUES(:N,:V)                  01g03pruhphqc            1 00000000AAA47348          1    <<----對應PL/SQL程式碼2

INSERT INTO T_BG_20170610_LHR VALUES(:N,:V)                  01g03pruhphqc            2 00000000AAAF7A28          3    <<----對應PL/SQL程式碼3、4、5

INSERT INTO T_BG_20170610_LHR VALUES(:N,:V)                  01g03pruhphqc            3 0000000095DA4B00          1    <<----對應PL/SQL程式碼6

 

LHR@orclasm > SELECT d.SQL_ID,D.CHILD_NUMBER,D.BIND_LENGTH_UPGRADEABLE FROM V$SQL_SHARED_CURSOR D WHERE D.SQL_ID = '01g03pruhphqc';

 

SQL_ID        CHILD_NUMBER B

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

01g03pruhphqc            0 N

01g03pruhphqc            1 Y

01g03pruhphqc            2 Y

01g03pruhphqc            3 Y

下面查詢分配的記憶體空間大小：

LHR@orclasm > SELECT B.CHILD_NUMBER,B.CHILD_ADDRESS,D.BIND_NAME,D.POSITION,D.DATATYPE,D.MAX_LENGTH FROM v$sql_bind_metadata d,V$SQL b WHERE d.ADDRESS=b.CHILD_ADDRESS AND b.SQL_ID='01g03pruhphqc' ORDER BY B.CHILD_NUMBER,D.POSITION;

 

CHILD_NUMBER CHILD_ADDRESS    BIND_NAME                        POSITION   DATATYPE MAX_LENGTH

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

           0 00000000AA902CE8 N                                       1          2         22

           0 00000000AA902CE8 V                                       2          1         32

           1 00000000AAA47348 N                                       1          2         22

           1 00000000AAA47348 V                                       2          1        128

           2 00000000AAAF7A28 N                                       1          2         22

           2 00000000AAAF7A28 V                                       2          1       2000

           3 0000000095DA4B00 N                                       1          2         22

           3 0000000095DA4B00 V                                       2          1       4000

從上述查詢結果可以看到，Child Cursor 0中文字型繫結變數V確實被分配了32位元組的記憶體空間，Child Cursor 1中文字型繫結變數V確實被分配了128位元組的記憶體空間，Child Cursor 2中文字型繫結變數V被分配了2000位元組的記憶體空間，Child Cursor 3中文字型繫結變數V被分配了4000位元組的記憶體空間，同時這三個Child Cursor中的數值型繫結變數N統一被分配了22位元組的記憶體空間。

通過上述示例可以看出：為了避免不必要的硬解析，在PL/SQL程式碼中處理帶文字型繫結變數的目標SQL時，應該將這些文字型繫結變數的定義長度保持在同一個等級，當然，這裡最好是定義成一個統一的長度，比如VARCHAR2(4000)。

---

---

繫結變數分級是oracle在pl/sql中會根據文字型別的繫結變數的定義長度而將這些文字型繫結變數分為四個等級：

1 定義長度在32位元組（bytes)以內的文字型繫結變數被分在第一個等級，oracle為其分配32位元組的記憶體空間
2 定義長度在33-128位元組之間的被分為第二個等級，oracle為其分配128位元組的記憶體空間
3 定義長度在129-2000位元組之間的文字型被分為在第三個等級，oracle為其分配2000位元組的記憶體空間
4 定義長度在2000位元組以上的被分為在第四個等級，取決於對應文字型別的繫結變數鎖傳入的實際繫結變數的大小，如果實際傳入的繫結變數的值小於或者等於2000，oracle為其分配2000位元組的記憶體空間，如果實際傳入的繫結變數大於2000位元組，則oracle為其分配4000位元組的記憶體空間

需要注意的是這裡的繫結變數分級僅僅適用於文字型別的繫結變數，對於number型別的繫結變數oracle統一為其分配22位元組的記憶體空間。

自己在11.2.0.4環境中對於SQL語句測試繫結變數分級，發現並不絕對遵守上面的規則來生成child cursor，當然還是建議SQL語句或者PL/SQL中對於相同的繫結變數，申明的資料型別和長度必須統一，避免因為繫結變數分級而產生過多的child cursor。其原因是因為child cursor不僅僅儲存瞭解析樹和執行計劃，還會儲存該SQL所使用的繫結變數型別和長度，如果該SQL的繫結變數型別和長度發生變化，則oracle將無法重用該child cursor，必須為該SQL再生成一個child cursor。

繫結變數何時被捕獲：
1 含有繫結變數的sql語句被硬解析時
2 當含有繫結變數的sql語句以軟解析或者軟軟解析方式重複執行時，該sql語句中的繫結變數的具體輸入值也可能被oracle捕獲，只不過預設情況下這種捕獲操作oracle需要間隔15分鐘才會做一次

需要注意的是oracle只會捕獲那些位於目標sql語句的where條件中的繫結變數的具體輸入值，而對於那些使用了繫結變數的insert語句，不管insert語句是否以硬解析方式執行，oracle始終不會捕獲其value子句中對應的繫結變數的具體輸入值。

我們重新設定下繫結變數的具體值，再次執行上述sql，這裡oracle並沒有馬上捕獲sql語句中新的繫結變數的具體值，這個是因為軟解析和如軟軟解析時，oracle為了考慮效能等問題需要間隔15分鐘後才能再次捕獲繫結變數的具體值：

這裡提到關於繫結變數的問題，這裡再提一下何種謂詞適合用繫結變數:一般而言對於主鍵、id、流水號等基本唯一的值，並且對應的SQL執行頻率特別高，oracle建議使用繫結變數，而對於那些狀態值（例如能夠列舉的，不同值只有幾個或者數十個）不建議使用繫結變數，因為這類SQL即使不使用繫結變數，oracle的解析消耗也是微乎其微，而如果使用繫結變數，會導致在某些特定場景下SQL執行計劃並不是最合理的，甚至是非常糟糕的，所以也不要將系統中所有的SQL都使用繫結變數，這裡簡單做個測試以供大家參考：

對於object_type=’INDEX’優化器更應該選擇全表掃描，而不應該選擇索引範圍掃描，但是因為使用的繫結變數，然後又關閉了繫結變數窺視，則優化器計算選擇率時只能是1/num_distinct，進而導致rows評估也存在較大的誤差，從而選擇了資源消耗更多的索引範圍掃描；如果開啟了窺視，又沒有自適應遊標等特性支撐，會導致後續的SQL都使用之前的執行計劃，又是一個效能隱患。

使用繫結變數欄位型別：
xiaoyu個人是不太建議狀態欄位、時間範圍欄位等使用繫結變數的，這類SQL如果執行頻率又不高，不存在較大的併發，即使不寫成繫結變數，也只有幾十個sql_id在shared pool中，這點解析消耗微乎其微，而如果走錯一個不合理的執行計劃，則消耗的資源將遠遠超過這點解析消耗。

可以參考之前寫的關於sql程式碼中哪些列適合使用繫結變數 http://www.dbaxiaoyu.com/archives/2534
地址：http://www.dbaxiaoyu.com/archives/2750