绑定变量知识—绑定变量的分级和捕获

绑定变量分级是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中,这点解析消耗微乎其微,而如果走错一个不合理的执行计划,则消耗的资源将远远超过这点解析消耗。

可以参考之前写的关于sql代码中哪些列适合使用绑定变量 http://www.dbaxiaoyu.com/archives/2534

About xiaoyu

xiaoyu,享受数据库带给xiaoyu的乐趣! 13439818916@163.com 欢迎邮件联系讨论
This entry was posted in oracle. Bookmark the permalink.