[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個子游標.也許上次沒講明白,做為一個補充.