[20161001]綁定變量的配置設定長度5.txt

[20161001]綁定變量的配置設定長度5.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.

--//前面我的測試

--字元串長度變化是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"=8000 scope=memory;

alter system set "_cursor_bind_capture_area_size"=8000 scope=memory

*

ERROR at line 1:

ORA-00068: invalid value 8000 for parameter _cursor_bind_capture_area_size, must be between 0 and 3999

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;

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);

   v_c1 := rpad('2',128);

   v_c2 := rpad('2',32);

   v_c1 := rpad('3',128);

   v_c2 := rpad('3',128);

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

                         1          1

                         2          2

--僅僅3個子光标，why?

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 2016-10-01 19:42:46 VARCHAR2(32)    0

                           YES :INSTRING2                    2         32 2016-10-01 19:42:46 VARCHAR2(32)    0

                         1 YES :INSTRING1                    1         32 2016-10-01 19:42:46 VARCHAR2(32)    1

                           YES :INSTRING2                    2        128 2016-10-01 19:42:46 VARCHAR2(128)   1

                         2 YES :INSTRING1                    1        128 2016-10-01 19:42:46 VARCHAR2(128)   2

                           YES :INSTRING2                    2        128 2016-10-01 19:42:46 VARCHAR2(128)   2

6 rows selected.

--//會是因為第2次執行時已經指派v_c2 := rpad('1',128)。

3.建立腳本友善測試:

D:\tools\rlwrap> cat bbb.sql

   execute immediate 'select count(*) from t where c1=:instring1 and c2=:instring2' using rpad('1',&&1),rpad('1',&&2);

--quit;

alter system flush shared_pool;

@ bbb.sql 1 1

@ bbb.sql 1 33

@ bbb.sql 33 1

@ bbb.sql 33 33

SCOTT@test01p> @bind_cap 2z6faqbzrf9jg

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

2z6faqbzrf9jg            0 YES :INSTRING1                    1         32 2016-10-01 21:12:53 VARCHAR2(32)    1

                           YES :INSTRING2                    2         32 2016-10-01 21:12:53 VARCHAR2(32)    1

                         1 YES :INSTRING1                    1         32 2016-10-01 21:12:53 VARCHAR2(32)    1

                           YES :INSTRING2                    2        128 2016-10-01 21:12:53 VARCHAR2(128)   1

                         2 YES :INSTRING1                    1        128 2016-10-01 21:12:53 VARCHAR2(128)   1

--//依舊是3個子光标。

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

2z6faqbzrf9jg            0 YES :INSTRING1                    1         32 2016-10-01 21:15:07 VARCHAR2(32)    1

                           YES :INSTRING2                    2         32 2016-10-01 21:15:07 VARCHAR2(32)    1

                         1 YES :INSTRING1                    1        128 2016-10-01 21:15:07 VARCHAR2(128)   1

                         2 YES :INSTRING1                    1        128 2016-10-01 21:15:07 VARCHAR2(128)   1

                           YES :INSTRING2                    2        128 2016-10-01 21:15:07 VARCHAR2(128)   1

--視乎從上面的測試得到一個規律,文字比較難描述。

--當執行@ bbb.sql 1 33,如果沒有其他子光标的情況下應該如下：

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

2z6faqbzrf9jg            0 YES :INSTRING1                    1         32 2016-10-01 21:34:50 VARCHAR2(32)    1

                           YES :INSTRING2                    2        128 2016-10-01 21:34:50 VARCHAR2(128)   1

--而因為存在子光标CHILD_NUMBER=1

2z6faqbzrf9jg            1 YES :INSTRING1                    1        128 2016-10-01 21:15:07 VARCHAR2(128)   1

--這樣生成新的子光标DATATYPE_STRING都是VARCHAR2(128)，也就是生成的DATATYPE_STRING要大于前面的子光标。

4.根據這個規律，在排列組合看看：

@bbb.sql 1 1

@bbb.sql 1 33

@bbb.sql 33 1

@bbb.sql 33 33

@bbb.sql 1 129

@bbb.sql 33 129

@bbb.sql 129 1

@bbb.sql 129 33

@bbb.sql 129 129

@bbb.sql 1 2001

@bbb.sql 33 2001

@bbb.sql 129 2001

@bbb.sql 2001 1

@bbb.sql 2001 33

@bbb.sql 2001 129

@bbb.sql 2001 2001

--按照這個組合應該是7個子光标。

2z6faqbzrf9jg            0 YES :INSTRING1                    1         32 2016-10-01 21:53:03 VARCHAR2(32)    1

                           YES :INSTRING2                    2         32 2016-10-01 21:53:03 VARCHAR2(32)    1

                         1 YES :INSTRING1                    1         32 2016-10-01 21:53:03 VARCHAR2(32)    1

                           YES :INSTRING2                    2        128 2016-10-01 21:53:03 VARCHAR2(128)   1

                         2 YES :INSTRING1                    1        128 2016-10-01 21:53:03 VARCHAR2(128)   1

                         3 YES :INSTRING1                    1        128 2016-10-01 21:53:03 VARCHAR2(128)   1

                           YES :INSTRING2                    2       2000 2016-10-01 21:53:03 VARCHAR2(2000)  1

                         4 YES :INSTRING1                    1       2000 2016-10-01 21:53:03 VARCHAR2(2000)  1

                         5 YES :INSTRING1                    1       2000 2016-10-01 21:53:03 VARCHAR2(2000)  1

                           YES :INSTRING2                    2       4000 2016-10-01 21:53:03 VARCHAR2(4000)  1

                         6 YES :INSTRING1                    1       4000 2016-10-01 21:53:03 VARCHAR2(4000)  1

14 rows selected.

                         3          2

                         4          3

                         5          3

                         6          4

7 rows selected.

--總結：

--大家可以嘗試各種組合，我的測試最大就是7個。

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

2z6faqbzrf9jg            0 YES :INSTRING1                    1         32 2016-10-01 22:00:32 VARCHAR2(32)    1

                           YES :INSTRING2                    2         32 2016-10-01 22:00:32 VARCHAR2(32)    1

                         1 YES :INSTRING1                    1         32 2016-10-01 22:00:32 VARCHAR2(32)    1

                           YES :INSTRING2                    2        128 2016-10-01 22:00:32 VARCHAR2(128)   1

                         2 YES :INSTRING1                    1         32 2016-10-01 22:00:32 VARCHAR2(32)    1

                           YES :INSTRING2                    2       2000 2016-10-01 22:00:32 VARCHAR2(2000)  1

                         3 YES :INSTRING1                    1         32 2016-10-01 22:00:32 VARCHAR2(32)    1

                           YES :INSTRING2                    2       4000 2016-10-01 22:00:32 VARCHAR2(4000)  1

                         4 YES :INSTRING1                    1        128 2016-10-01 22:00:32 VARCHAR2(128)   1

                         5 YES :INSTRING1                    1       2000 2016-10-01 22:00:33 VARCHAR2(2000)  1

                           YES :INSTRING2                    2       4000 2016-10-01 22:00:33 VARCHAR2(4000)  1

                         6 YES :INSTRING1                    1       4000 2016-10-01 22:00:33 VARCHAR2(4000)  1

                         2          1

                         3          1

                         4          4

                         5          4