Oracle绑定变量分级（Bind Graduation）

绑定变量分级（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字节的内存空间。

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE 11.2.0.4.0 Production
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production

SQL_TEXT1:
declare
n number(10);
v varchar2(32);
begin
n:=1;
v:='xiaoyu';
execute immediate 'insert into t values(:n,:v)' using n,v;
commit;
end;
/

SQL_TEXT2:
declare
n number(10);
v varchar2(80);
begin
n:=1;
v:='xiaoyu';
execute immediate 'insert into t values(:n,:v)' using n,v;
commit;
end;
/

SQL_TEXT3:
declare
n number(10);
v varchar2(2000);
begin
n:=1;
v:='xiaoyu';
execute immediate 'insert into t values(:n,:v)' using n,v;
commit;
end;
/

SQL_TEXT4:
declare
n number(10);
v varchar2(4000);
begin
n:=1;
v:='xiaoyu';
execute immediate 'insert into t values(:n,:v)' using n,v;
commit;
end;
/

SQL_TEXT5:
declare
n number(10);
v varchar2(4000);
begin
n:=1;
v:=rpad('xiaoyu',3000);
execute immediate 'insert into t values(:n,:v)' using n,v;
commit;
end;
/

SQL> select sql_text,sql_id,version_count,executions from v$sqlarea where sql_text like 'insert into t%';

SQL_TEXT SQL_ID VERSION_COUNT EXECUTIONS
---------------------------------------- ------------- ------------- ----------
insert into t values(:n,:v) 21mycdpm39kzv 3 5

SQL> select sql_id,child_number,child_address,executions from v$sql where sql_id='21mycdpm39kzv';

SQL_ID CHILD_NUMBER CHILD_ADDRESS EXECUTIONS
------------- ------------ ---------------- ----------
21mycdpm39kzv 0 000000008A758A48 1
21mycdpm39kzv 1 000000008A755890 3
21mycdpm39kzv 2 000000008A753240 1

SQL> select address,bind_name,position,datatype,max_length from v$sql_bind_metadata where address in ('000000008A758A48','000000008A755890','000000008A753240') order by address,position;

ADDRESS BIND_NAME POSITION DATATYPE MAX_LENGTH
---------------- ------------------------------ ---------- ---------- ----------
000000008A753240 N 1 2 22
000000008A753240 V 2 1 4000
000000008A755890 N 1 2 22
000000008A755890 V 2 1 128
000000008A758A48 N 1 2 22
000000008A758A48 V 2 1 32

6 rows selected.

自己在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> variable vnum number;
SQL> exec :vnum:=100;

PL/SQL procedure successfully completed.

SQL> insert into t100 values(:vnum,'acb');

1 row created.

SQL> select sql_id,sql_text from v$sql where sql_text like 'insert into t100%';

SQL_ID
-------------
SQL_TEXT
--------------------------------------------------------------------------------
5cyjjwy6y74t3
insert into t100 values(:vnum,'acb')

SQL> select sql_id,name,position,datatype_string,last_captured,value_string from v$sql_bind_capture where sql_id='5cyjjwy6y74t3';

SQL_ID NAME POSITION DATATYPE_STRING LAST_CAPTURED VALUE_STRING
------------- -------------------- ---------- ------------------------------------------------------------ ------------------- ------------------------------
5cyjjwy6y74t3 :VNUM 1 NUMBER

这里看出来即使该sql语句是硬解析，由于绑定变量不在sql语句的where条件中，并且是insert into values的语句，oracle并不会捕获values后面绑定变量的值。

而如果我们换成下面这种形式，不以insert into values的形式，则oracle是可以抓取到where后面的绑定变量的具体值的：
SQL> insert into t100 select id,name from t100 where id=:vnum;

2 rows created.

SQL> select sql_id,sql_text from v$sql where sql_text like 'insert into t100 select id,name from t100 where id=:vnum';

SQL_ID
-------------
SQL_TEXT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
a9v1314t50dwg
insert into t100 select id,name from t100 where id=:vnum

SQL> select sql_id,name,policesition,datatype_string,last_captured,value_string from v$sql_bind_capture where sql_id='a9v1314t50dwg';

SQL_ID NAME POSITION DATATYPE_STRING LAST_CAPTURED VALUE_STRING
------------- -------------------- ---------- ------------------------------------------------------------ ------------------- ------------------------------
a9v1314t50dwg :VNUM 1 NUMBER 2015-07-01 01:37:50 100

我们重新设置下绑定变量的具体值，再次执行上述sql，这里oracle并没有马上捕获sql语句中新的绑定变量的具体值，这个是因为软解析和如软软解析时，oracle为了考虑性能等问题需要间隔15分钟后才能再次捕获绑定变量的具体值：

SQL> exec :vnum:=1;

PL/SQL procedure successfully completed.

SQL> insert into t100 select id,name from t100 where id=:vnum;

1 row created.

SQL> select sql_id,name,position,datatype_string,last_captured,value_string from v$sql_bind_capture where sql_id='a9v1314t50dwg';

SQL_ID NAME POSITION DATATYPE_STRING LAST_CAPTURED VALUE_STRING
------------- -------------------- ---------- ------------------------------------------------------------ ------------------- ------------------------------
a9v1314t50dwg :VNUM 1 NUMBER 2015-07-01 01:37:50 100

过了15分钟后再次执行sql，此时这个sql虽然是软解析，oracle还是会再次捕获这个绑定变量的传入值：
SQL> exec :vnum:=1;

PL/SQL procedure successfully completed.

SQL> insert into t100 select id,name from t100 where id=:vnum;

2 rows created.

SQL> select sql_id,name,position,datatype_string,last_captured,value_string from v$sql_bind_capture where sql_id='a9v1314t50dwg';

SQL_ID NAME POSITION DATATYPE_STRING LAST_CAPTURED VALUE_STRING
------------- -------------------- ---------- ------------------------------------------------------------ ------------------- ------------------------------
a9v1314t50dwg :VNUM 1 NUMBER 2015-07-01 01:53:05 1

这里提到关于绑定变量的问题，这里再提一下何种谓词适合用绑定变量:一般而言对于主键、id、流水号等基本唯一的值，并且对应的SQL执行频率特别高，oracle建议使用绑定变量，而对于那些状态值（例如能够枚举的，不同值只有几个或者数十个）不建议使用绑定变量，因为这类SQL即使不使用绑定变量，oracle的解析消耗也是微乎其微，而如果使用绑定变量，会导致在某些特定场景下SQL执行计划并不是最合理的，甚至是非常糟糕的，所以也不要将系统中所有的SQL都使用绑定变量，这里简单做个测试以供大家参考：

SQL> select * from v$version where rownum=1;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
SQL> create table t01 as select object_id,object_name,object_type from dba_objects;

Table created.
SQL> update t01 set object_type='INDEX' where object_id<90000;

86872 rows updated.

SQL> commit;

Commit complete.

这里模拟出object_type列数据分布存在倾斜性

SQL> select object_type,count(*) from t01 group by object_type;

OBJECT_TYPE COUNT(*)
------------------- ----------
INDEX PARTITION 21
TABLE PARTITION 21
DATABASE LINK 2
LOB 1
TRIGGER 1
INDEX 86894
TABLE 34
VIEW 2

8 rows selected.
SQL> create index ind_t01_objtype on t01(object_type);

Index created.
SQL> execute dbms_stats.gather_table_stats(ownname=>'SYS',tabname=>'T01',method_opt=>'FOR ALL COLUMNS SIZE 254');

PL/SQL procedure successfully completed.
SQL> alter session set "_optim_peek_user_binds"=false;

Session altered.

这里关闭掉绑定变量窥视，其实xiaoyu个人觉得在oracle 11g之前都应该关闭掉绑定变量窥视，而在oracle 11g之后推出了自适应游标，部分系统可以考虑开启窥视和自适应游标，但是xiaoyu维护的很多大型在线系统对于这两个特性都是选择禁掉，自适应游标有较多的bug，具体可以参考mos上的部分文章。
SQL> variable type varchar2(128);
SQL> exec :type:='INDEX';

PL/SQL procedure successfully completed.
SQL> select * from t01 where object_type=:type;

86894 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 2537206454

-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10872 | 382K| 93 (0)| 00:00:02 |
| 1 | TABLE ACCESS BY INDEX ROWID| T01 | 10872 | 382K| 93 (0)| 00:00:02 |
|* 2 | INDEX RANGE SCAN | IND_T01_OBJTYPE | 10872 | | 26 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("OBJECT_TYPE"=:TYPE)

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
12259 consistent gets
0 physical reads
0 redo size
4607541 bytes sent via SQL*Net to client
64235 bytes received via SQL*Net from client
5794 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
86894 rows processed

SQL> select * from t01 where object_type='INDEX';

86894 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 3295674804

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 86928 | 3056K| 144 (1)| 00:00:02 |
|* 1 | TABLE ACCESS FULL| T01 | 86928 | 3056K| 144 (1)| 00:00:02 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("OBJECT_TYPE"='INDEX')

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
6277 consistent gets
0 physical reads
0 redo size
3342833 bytes sent via SQL*Net to client
64235 bytes received via SQL*Net from client
5794 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
86894 rows processed

对于object_type=’INDEX’优化器更应该选择全表扫描，而不应该选择索引范围扫描，但是因为使用的绑定变量，然后又关闭了绑定变量窥视，则优化器计算选择率时只能是1/num_distinct，进而导致rows评估也存在较大的误差，从而选择了资源消耗更多的索引范围扫描；如果开启了窥视，又没有自适应游标等特性支撑，会导致后续的SQL都使用之前的执行计划，又是一个性能隐患。

使用绑定变量字段类型：
xiaoyu个人是不太建议状态字段、时间范围字段等使用绑定变量的，这类SQL如果执行频率又不高，不存在较大的并发，即使不写成绑定变量，也只有几十个sql_id在shared pool中，这点解析消耗微乎其微，而如果走错一个不合理的执行计划，则消耗的资源将远远超过这点解析消耗。

Oracle绑定变量分级（Bind Graduation）

Oracle绑定变量分级（Bind Graduation）

更多相关文章

随机推荐