[20171021]绑定变量的分配长度8.txt
--//前几天跟别人讨论,提到我写的测试链接
http://blog.itpub.net/267265/viewspace-2125825/
--//很有意思.当时实际上自己也是在没仔细探究,实际上也很混乱.今天重复测试看看.
1.环境：
SCOTT@test01p> @ ver1
PORT_STRING VERSION BANNER CON_ID
------------------------------ -------------- -------------------------------------------------------------------------------- ----------
IBMPC/WIN_NT64-9.1.0 12.1.0.1.0 Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production 0
SCOTT@test01p> create table t (c1 varchar2(4000),c2 varchar2(4000),c3 varchar2(4000),c4 varchar2(4000),c5 varchar2(4000));
Table created.
--//前面我的测试
--字符串长度变化是32，32+96=128,32+96+1872=2000.也就是分4个段 1-32,33-128,129-2000,2001-4000.
--这样如果2个字段varchar2(4000)，理论讲可以出现4*4=16个子光标，测试看看是否正确。
SYS@test> alter system set "_cursor_bind_capture_area_size"=3999 scope=memory;
System altered.
--//改变 _cursor_bind_capture_area_size，保证能捕获绑定变量的值。
2.测试脚本一：
SCOTT@test01p> alter system flush shared_pool;
System altered.
declare
v_c1 varchar2(4000);
v_c2 varchar2(4000);
begin
v_c1 := rpad('0',32);
v_c2 := rpad('0',32);
execute immediate 'select count(*) from t where c1=:instring1 and c2=:instring2' using v_c1,v_c2 ;
v_c1 := rpad('1',32);
v_c2 := rpad('1',128);
execute immediate 'select count(*) from t where c1=:instring1 and c2=:instring2' using v_c1,v_c2 ;
v_c1 := rpad('2',128);
v_c2 := rpad('2',32);
execute immediate 'select count(*) from t where c1=:instring1 and c2=:instring2' using v_c1,v_c2 ;
-- v_c1 := rpad('3',128);
-- v_c2 := rpad('3',128);
-- execute immediate 'select count(*) from t where c1=:instring1 and c2=:instring2' using v_c1,v_c2 ;
end;
/
commit;
--确定sql_id='2z6faqbzrf9jg'.
SCOTT@test01p> select sql_id, child_number, executions from v$sql where sql_id = '2z6faqbzrf9jg';
SQL_ID CHILD_NUMBER EXECUTIONS
------------- ------------ ----------
2z6faqbzrf9jg 0 1
2z6faqbzrf9jg 1 1
2z6faqbzrf9jg 2 1
SCOTT@test01p> @ bind_cap 2z6faqbzrf9jg ''
C200
-----------------------------------------------------------------------------------------------------------------------------------------
select count(*) from t where c1=:instring1 and c2=:instring2
SQL_ID CHILD_NUMBER WAS NAME POSITION MAX_LENGTH LAST_CAPTURED DATATYPE_STRING VALUE_STRING
------------- ------------ --- -------------------- ---------- ---------- ------------------- --------------- ---------------------------
2z6faqbzrf9jg 0 YES :INSTRING1 1 32 2017-10-21 20:37:54 VARCHAR2(32) 0
YES :INSTRING2 2 32 2017-10-21 20:37:54 VARCHAR2(32) 0
1 YES :INSTRING1 1 32 2017-10-21 20:37:54 VARCHAR2(32) 1
YES :INSTRING2 2 128 2017-10-21 20:37:54 VARCHAR2(128) 1
2 YES :INSTRING1 1 128 2017-10-21 20:37:54 VARCHAR2(128) 2
YES :INSTRING2 2 128 2017-10-21 20:37:54 VARCHAR2(128) 2
6 rows selected.
--//注意看我第2次执行带入参数是
v_c1 := rpad('2',128);
v_c2 := rpad('2',32);
--//而实际上CHILD_NUMBER=2.显示类型都是VARCHAR2(128).我当时的理解就是第2次执行时赋值v_c2 := rpad('1',128);
--//这样在第3次执行时,要扫描找到合适的子光标来执行语句,很明显前面2个都不合适.建立CHILD_NUMBER=2的子光标.这样选择
--//前面最大的长度继承下来.
3.测试脚本二：
SCOTT@test01p> alter system flush shared_pool;
System altered.
declare
v_c1 varchar2(4000);
v_c2 varchar2(4000);
begin
v_c1 := rpad('0',32);
v_c2 := rpad('0',32);
execute immediate 'select count(*) from t where c1=:instring1 and c2=:instring2' using v_c1,v_c2 ;
v_c1 := rpad('1',32);
v_c2 := rpad('1',2000);
execute immediate 'select count(*) from t where c1=:instring1 and c2=:instring2' using v_c1,v_c2 ;
v_c1 := rpad('2',128);
v_c2 := rpad('2',32);
execute immediate 'select count(*) from t where c1=:instring1 and c2=:instring2' using v_c1,v_c2 ;
end;
/
--//执行这样的脚本,第3次执行脚本bind_cap,抓取的类型应该是VARCHAR2(128),VARCHAR2(2000).
--//继续测试看看.
SCOTT@test01p> @ bind_cap 2z6faqbzrf9jg ''
C200
-------------------------------------------------------------------------------------------------------------------------
select count(*) from t where c1=:instring1 and c2=:instring2
SQL_ID CHILD_NUMBER WAS NAME POSITION MAX_LENGTH LAST_CAPTURED DATATYPE_STRING VALUE_STRING
------------- ------------ --- -------------------- ---------- ---------- ------------------- --------------- -------------
2z6faqbzrf9jg 0 YES :INSTRING1 1 32 2017-10-21 21:00:24 VARCHAR2(32) 0
YES :INSTRING2 2 32 2017-10-21 21:00:24 VARCHAR2(32) 0
1 YES :INSTRING1 1 32 2017-10-21 21:00:24 VARCHAR2(32) 1
YES :INSTRING2 2 2000 2017-10-21 21:00:24 VARCHAR2(2000) 1
2 YES :INSTRING1 1 128 2017-10-21 21:00:24 VARCHAR2(128) 2
YES :INSTRING2 2 2000 2017-10-21 21:00:24 VARCHAR2(2000) 2
6 rows selected.
--//看CHILD_NUMBER=2的行,正好符合我的推测.
--//oracle这样设计的目的一定程度减少了子光标的数量.我前面的测试这样最多产生7个子光标.也许上次没讲明白,做为一个补充.