绑定变量分级（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