[20161002]绑定变量的分配长度6.txt

[20161002]绑定变量的分配长度6.txt

--如果绑定变量中字符串分配占用空间的长度变化，oracle会建立子光标。
--一般如果绑定变量有多个字段在分配占用空间时长度变化，这样生成的子光标会增加。
--我以前的测试字符串长度变化是32，32+96=128,32+96+1872=2000.也就是分4个段 1-32,33-128,129-2000,2001-4000.
--相关链接：
http://blog.itpub.net/267265/viewspace-1993495/
http://blog.itpub.net/267265/viewspace-2024389/
http://blog.itpub.net/267265/viewspace-2050886/
http://blog.itpub.net/267265/viewspace-2056695/

--本测试看看2个绑定变量能产生多少子光标，按照道理应该产生4*4=16个子光标。实际上别人的测试并不会产生16个子光标，
--出于好奇没事，我自己也测试看看。


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.

SYS@test> alter system set "_cursor_bind_capture_area_size"=3999 scope=memory;
System altered.
--//改变 _cursor_bind_capture_area_size，保证能捕获绑定变量的值。
--//今天测试使用3个绑定变量的情况.

2.安装前面的测试,各种排列组合,应该总和最小的排列前面.

--//建立执行脚本bbc.sql:
D:\tools\rlwrap> cat bbc.sql
declare
--v_c1 varchar2(4000);
--v_c2 varchar2(4000);
begin
   execute immediate 'select count(*) from t where c1=:s1 and c2=:s2 and c3=:s3' using rpad('1',&&1),rpad('1',&&2),rpad('1',&&3);
end;
/
--quit;

alter system flush shared_pool;
alter system flush shared_pool;

WITH x1
     AS (SELECT 1 a, 1 id FROM DUAL
         UNION ALL
         SELECT 2 a, 33 id FROM DUAL
         UNION ALL
         SELECT 3 a, 129 id FROM DUAL
         UNION ALL
         SELECT 4, 2001 id FROM DUAL)
    ,x2
     AS (SELECT t1.a a1
               ,t2.a a2
               ,t3.a a3
               ,t1.id id1
               ,t2.id id2
               ,t3.id id3
           FROM x1 t1, x1 t2 ,x1 t3)
select '@bbc '||id1||' '||id2||' '||id3  from x2 order by a1+a2+a3,a1,a2,a3;

--//你可以做各种排序。sql_id='bkkt9u3a2824q'.

SCOTT@test01p> select sql_id, child_number, executions  from v$sql where sql_id = 'bkkt9u3a2824q';
SQL_ID        CHILD_NUMBER EXECUTIONS
------------- ------------ ----------
bkkt9u3a2824q            0          1
bkkt9u3a2824q            1          1
bkkt9u3a2824q            2          1
bkkt9u3a2824q            3          1
bkkt9u3a2824q            4          2
bkkt9u3a2824q            5          3
bkkt9u3a2824q            6          1
bkkt9u3a2824q            7          3
bkkt9u3a2824q            8          6
bkkt9u3a2824q            9         45
10 rows selected.

SCOTT@test01p> @ bind_cap bkkt9u3a2824q
C200
---------------------------------------------------------
select count(*) from t where c1=:s1 and c2=:s2 and c3=:s3

SQL_ID        CHILD_NUMBER WAS NAME                   POSITION MAX_LENGTH LAST_CAPTURED       DATATYPE_STRING VALUE_STRING
------------- ------------ --- -------------------- ---------- ---------- ------------------- --------------- ------------------------------
bkkt9u3a2824q            0 YES :S1                           1         32 2016-10-02 20:36:12 VARCHAR2(32)    1
                           YES :S2                           2         32 2016-10-02 20:36:12 VARCHAR2(32)    1
                           YES :S3                           3         32 2016-10-02 20:36:12 VARCHAR2(32)    1

                         1 YES :S1                           1         32 2016-10-02 20:36:12 VARCHAR2(32)    1
                           YES :S2                           2         32 2016-10-02 20:36:12 VARCHAR2(32)    1
                           YES :S3                           3        128 2016-10-02 20:36:12 VARCHAR2(128)   1

                         2 YES :S1                           1         32 2016-10-02 20:36:12 VARCHAR2(32)    1
                           YES :S2                           2        128 2016-10-02 20:36:12 VARCHAR2(128)   1
                           YES :S3                           3        128 2016-10-02 20:36:12 VARCHAR2(128)   1

                         3 YES :S1                           1        128 2016-10-02 20:36:12 VARCHAR2(128)   1
                           YES :S2                           2        128 2016-10-02 20:36:12 VARCHAR2(128)   1
                           YES :S3                           3        128 2016-10-02 20:36:12 VARCHAR2(128)   1

                         4 YES :S1                           1        128 2016-10-02 20:36:12 VARCHAR2(128)   1
                           YES :S2                           2        128 2016-10-02 20:36:12 VARCHAR2(128)   1
                           YES :S3                           3       2000 2016-10-02 20:36:12 VARCHAR2(2000)  1

                         5 YES :S1                           1        128 2016-10-02 20:36:12 VARCHAR2(128)   1
                           YES :S2                           2       2000 2016-10-02 20:36:12 VARCHAR2(2000)  1
                           YES :S3                           3       2000 2016-10-02 20:36:12 VARCHAR2(2000)  1

                         6 YES :S1                           1       2000 2016-10-02 20:36:12 VARCHAR2(2000)  1
                           YES :S2                           2       2000 2016-10-02 20:36:12 VARCHAR2(2000)  1
                           YES :S3                           3       2000 2016-10-02 20:36:12 VARCHAR2(2000)  1

                         7 YES :S1                           1       2000 2016-10-02 20:36:12 VARCHAR2(2000)  1
                           YES :S2                           2       2000 2016-10-02 20:36:12 VARCHAR2(2000)  1
                           YES :S3                           3       4000 2016-10-02 20:36:12 VARCHAR2(4000)  1

                         8 YES :S1                           1       2000 2016-10-02 20:36:12 VARCHAR2(2000)  1
                           YES :S2                           2       4000 2016-10-02 20:36:12 VARCHAR2(4000)  1
                           YES :S3                           3       4000 2016-10-02 20:36:12 VARCHAR2(4000)  1

                         9 YES :S1                           1       4000 2016-10-02 20:36:13 VARCHAR2(4000)  1
                           YES :S2                           2       4000 2016-10-02 20:36:13 VARCHAR2(4000)  1
                           YES :S3                           3       4000 2016-10-02 20:36:13 VARCHAR2(4000)  1

30 rows selected.

--仅仅出现10个子光标。

WITH x1
     AS (SELECT 1 a, 1 id FROM DUAL
         UNION ALL
         SELECT 2 a, 33 id FROM DUAL
         UNION ALL
         SELECT 3 a, 129 id FROM DUAL
         UNION ALL
         SELECT 4, 2001 id FROM DUAL)
    ,x2
     AS (SELECT t1.a a1
               ,t2.a a2
               ,t3.a a3
               ,t1.id id1
               ,t2.id id2
               ,t3.id id3
           FROM x1 t1, x1 t2 ,x1 t3)
select '@bbc '||id1||' '||id2||' '||id3  from x2 order by a1,a2,a3;

SCOTT@test01p> select sql_id, child_number, executions  from v$sql where sql_id = 'bkkt9u3a2824q';
SQL_ID        CHILD_NUMBER EXECUTIONS
------------- ------------ ----------
bkkt9u3a2824q            0          1
bkkt9u3a2824q            1          1
bkkt9u3a2824q            2          1
bkkt9u3a2824q            3          1
bkkt9u3a2824q            4          4
bkkt9u3a2824q            5          4
bkkt9u3a2824q            6          4
bkkt9u3a2824q            7         16
bkkt9u3a2824q            8         16
bkkt9u3a2824q            9         16
10 rows selected.

总结:
--如果使用3个字符串绑定变量,也不会出现很多子光标,我的测试10个.2个字符串绑定变量是7个.
--可以这么理解:

当某个语句执行时绑定变量长度变化(主要是长度增加),oracle实际上是先扫描或者遍历各个子光标检查是否合适.
如果不合适,建立子光标,但是这时候否建立的子光标中允许绑定变量长度应该是当前存在光标中字符创长度最大的.这样一定程度减少子
光标数量.

实际上很少出现在select语句,因为select一定很少查询很长字符串的值.而是出现在insert,update语句.