【DB笔试面试583】在Oracle中，什么是绑定变量分级？
【DB笔试面试583】在Oracle中，什么是绑定变量分级？
♣
题目部分
在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程序员面试笔试宝典》，作者：李华荣。