《基于ORACLE的SQL优化读书》笔记 绑定变量分级

绑定变量分级：

Level1: 32

Level2:33-128

Level3:129-2000

Level4:2000+

分级只适用于文本类型，NUMBER类型固定为22.SQL文本没有变化，如果绑定变量的定义长度发生了变化，该SQL还是可能会做硬解析。在11.2.0.4上做了下实验。2000没有做出来。而且如果先有了N为4000的子CURSOR则不会再为小于4000的级别分配子CURSOR。实验如下

create table t (n number(10),v varchar2(3000));

declare

n number(10);

v varchar2(32767);

begin

n :=5;

v :='xxxxx';

execute immediate 'insert into t values(:n,:v)' using n,v;

commit;

end;

/

 

tester@AD_TEST> selectaddress,bind_name,position,datatype,max_length from v$sql_bind_metadata whereaddress ='00000000FE9F44D8' order by position;

 

ADDRESS          BIND_NAME                        POSITION   DATATYPE MAX_LENGTH

---------------- ------------------------------ -------------------- ----------

00000000FE9F44D8 N                                       1          2         22

00000000FE9F44D8 V                                      2          1         32

 

 

declare

n number(10);

v varchar2(2600);

begin

n :=5;

v :=rpad('xxxxxx',2500,'x');

execute immediate 'insert into t values(:n,:v)' using n,v;

commit;

end;

/

 

tester@AD_TEST> selectaddress,bind_name,position,datatype,max_length from v$sql_bind_metadata whereaddress ='00000000FEB57798' order by position;

 

ADDRESS         BIND_NAME                       POSITION   DATATYPE MAX_LENGTH

---------------- ------------------------------ -------------------- ----------

00000000FEB57798 N                                       1          2         22

00000000FEB57798 V                                       2          1       4000

 

declare

n number(10);

v varchar2(33);

begin

n :=5;

v :='xxxx35';

execute immediate 'insert into t values(:n,:v)' using n,v;

commit;

end;

/

tester@AD_TEST> selectaddress,bind_name,position,datatype,max_length from v$sql_bind_metadata whereaddress ='00000000FE9F44D8' order by position;

 

ADDRESS         BIND_NAME                       POSITION   DATATYPE MAX_LENGTH

---------------- ------------------------------ -------------------- ----------

00000000FE9F44D8 N                                       1          2         22

00000000FE9F44D8 V                                       2          1        128

 

declare

n number(10);

v varchar2(129);

begin

n :=5;

v :='xxxx35';

execute immediate 'insert into t values(:n,:v)' using n,v;

commit;

end;

/

select sql_text,sql_id,version_count,executions fromv$sqlarea where sql_text like 'insert into t%';

select sql_id,child_number,child_address from v$sql wheresql_id ='21mycdpm39kzv';

select address,bind_name,position,datatype,max_length fromv$sql_bind_metadata where address ='00000000FEB57798' order by position;