[20171021]绑定变量的分配长度8.txt

[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个子光标.也许上次没讲明白,做为一个补充.