【DB筆試面試583】在Oracle中，什麼是綁定變量分級？
- 2019 年 10 月 10 日
- 筆記
♣
題目部分
在Oracle中，什麼是綁定變量分級？
♣
答案部分
綁定變量分級（Bind Graduation）是指Oracle在PL/SQL代碼中會根據文本型綁定變量的定義長度而將這些文本型綁定變量分為四個等級，不同等級分配的內存大小不同，如下表所示：
|
等級 |
定義長度（Byte） |
分配內存大小（Byte） |
|---|---|---|
|
1 |
(0,32] |
32 |
|
2 |
[33,128] |
128 |
|
3 |
[129,2000] |
2000 |
|
4 |
大於2000 |
分配的內存空間大小取決於對應文本型綁定變量所傳入的實際綁定變量值的大小。如果實際傳入的綁定變量值小於或等於2000位元組，那麼Oracle會為其分配2000位元組的內存空間。如果實際傳入的綁定變量值大於2000位元組，那麼Oracle會為其分配4000位元組的內存空間。 |
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）中的解析樹和執行計劃就不能被重用了。原因是子游標中除了會存儲目標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)。
& 說明：
有關綁定變量分級的更多內容可以參考我的BLOG：http://blog.itpub.net/26736162/viewspace-2140560/
本文選自《Oracle程序員面試筆試寶典》，作者：李華榮。
