[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;
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
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
declare
v_c1 varchar2(4000);
v_c2 varchar2(4000);
begin
execute immediate 'select count(*) from t where c1=:instring1 and c2=:instring2' using rpad('1',&&1),rpad('1',&&2);
end;
/
--quit;
alter system flush shared_pool;
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
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 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
YES :INSTRING2 2 128 2016-10-01 21:12:53 VARCHAR2(128) 1
--//依舊是3個子游標。
alter system flush shared_pool;
@ bbb.sql 1 1
@ bbb.sql 33 1
@ bbb.sql 1 33
@ bbb.sql 33 33
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 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
YES :INSTRING2 2 32 2016-10-01 21:15:07 VARCHAR2(32) 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
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
--視乎從上面的測試得到一個規律,文字比較難描述。
--當執行@ bbb.sql 1 33,如果沒有其他子游標的情況下應該如下：
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 21:34:50 VARCHAR2(32) 1
YES :INSTRING2 2 128 2016-10-01 21:34:50 VARCHAR2(128) 1
--而因為存在子游標CHILD_NUMBER=1
SQL_ID CHILD_NUMBER WAS NAME POSITION MAX_LENGTH LAST_CAPTURED DATATYPE_STRING VALUE_STRING
------------- ------------ --- -------------------- ---------- ---------- ------------------- --------------- --------------------
2z6faqbzrf9jg 1 YES :INSTRING1 1 128 2016-10-01 21:15:07 VARCHAR2(128) 1
YES :INSTRING2 2 32 2016-10-01 21:15:07 VARCHAR2(32) 1
--這樣生成新的子游標DATATYPE_STRING都是VARCHAR2(128)，也就是生成的DATATYPE_STRING要大於前面的子游標。
4.根據這個規律，在排列組合看看：
alter system flush shared_pool;
@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個子游標。
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 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
YES :INSTRING2 2 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
YES :INSTRING2 2 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
YES :INSTRING2 2 4000 2016-10-01 21:53:03 VARCHAR2(4000) 1
14 rows selected.
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 2
4 3
5 3
6 4
7 rows selected.
--總結：
--大家可以嘗試各種組合，我的測試最大就是7個。
alter system flush shared_pool;
@bbb.sql 1 1
@bbb.sql 1 33
@bbb.sql 1 129
@bbb.sql 1 2001
@bbb.sql 33 1
@bbb.sql 33 33
@bbb.sql 33 129
@bbb.sql 33 2001
@bbb.sql 129 1
@bbb.sql 129 33
@bbb.sql 129 129
@bbb.sql 129 2001
@bbb.sql 2001 1
@bbb.sql 2001 33
@bbb.sql 2001 129
@bbb.sql 2001 2001
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 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
YES :INSTRING2 2 4000 2016-10-01 22:00:32 VARCHAR2(4000) 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
YES :INSTRING2 2 4000 2016-10-01 22:00:33 VARCHAR2(4000) 1
14 rows selected.
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 1
3 1
4 4
5 4
6 4
7 rows selected.
--如果繫結變數中字串分配佔用空間的長度變化，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;
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
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
declare
v_c1 varchar2(4000);
v_c2 varchar2(4000);
begin
execute immediate 'select count(*) from t where c1=:instring1 and c2=:instring2' using rpad('1',&&1),rpad('1',&&2);
end;
/
--quit;
alter system flush shared_pool;
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
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 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
YES :INSTRING2 2 128 2016-10-01 21:12:53 VARCHAR2(128) 1
--//依舊是3個子游標。
alter system flush shared_pool;
@ bbb.sql 1 1
@ bbb.sql 33 1
@ bbb.sql 1 33
@ bbb.sql 33 33
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 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
YES :INSTRING2 2 32 2016-10-01 21:15:07 VARCHAR2(32) 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
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
--視乎從上面的測試得到一個規律,文字比較難描述。
--當執行@ bbb.sql 1 33,如果沒有其他子游標的情況下應該如下：
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 21:34:50 VARCHAR2(32) 1
YES :INSTRING2 2 128 2016-10-01 21:34:50 VARCHAR2(128) 1
--而因為存在子游標CHILD_NUMBER=1
SQL_ID CHILD_NUMBER WAS NAME POSITION MAX_LENGTH LAST_CAPTURED DATATYPE_STRING VALUE_STRING
------------- ------------ --- -------------------- ---------- ---------- ------------------- --------------- --------------------
2z6faqbzrf9jg 1 YES :INSTRING1 1 128 2016-10-01 21:15:07 VARCHAR2(128) 1
YES :INSTRING2 2 32 2016-10-01 21:15:07 VARCHAR2(32) 1
--這樣生成新的子游標DATATYPE_STRING都是VARCHAR2(128)，也就是生成的DATATYPE_STRING要大於前面的子游標。
4.根據這個規律，在排列組合看看：
alter system flush shared_pool;
@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個子游標。
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 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
YES :INSTRING2 2 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
YES :INSTRING2 2 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
YES :INSTRING2 2 4000 2016-10-01 21:53:03 VARCHAR2(4000) 1
14 rows selected.
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 2
4 3
5 3
6 4
7 rows selected.
--總結：
--大家可以嘗試各種組合，我的測試最大就是7個。
alter system flush shared_pool;
@bbb.sql 1 1
@bbb.sql 1 33
@bbb.sql 1 129
@bbb.sql 1 2001
@bbb.sql 33 1
@bbb.sql 33 33
@bbb.sql 33 129
@bbb.sql 33 2001
@bbb.sql 129 1
@bbb.sql 129 33
@bbb.sql 129 129
@bbb.sql 129 2001
@bbb.sql 2001 1
@bbb.sql 2001 33
@bbb.sql 2001 129
@bbb.sql 2001 2001
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 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
YES :INSTRING2 2 4000 2016-10-01 22:00:32 VARCHAR2(4000) 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
YES :INSTRING2 2 4000 2016-10-01 22:00:33 VARCHAR2(4000) 1
14 rows selected.
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 1
3 1
4 4
5 4
6 4
7 rows selected.
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/267265/viewspace-2125825/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- [20160224]繫結變數的分配長度.txt變數
- [20171021]繫結變數的分配長度8.txt變數
- [20160313]繫結變數的分配長度4.txt變數
- [20171019]繫結變數的分配長度7.txt變數
- [20160302]繫結變數的分配長度2.txt變數
- [20160307]繫結變數的分配長度3.txt變數
- [20221103]繫結變數的分配長度11.txt變數
- [20221030]繫結變數的分配長度10.txt變數
- [20161002]繫結變數的分配長度6.txt變數
- 繫結變數變數
- 繫結變數之繫結變數窺探(Bind Peeking)變數
- Oracle 繫結變數Oracle變數
- [20121016]字串長度與繫結變數的子游標.txt字串變數
- 繫結變數的測試變數
- 繫結變數窺測的演變變數
- 檢視繫結變數變數
- 繫結變數窺測變數
- PLSQL使用繫結變數SQL變數
- Oracle之繫結變數Oracle變數
- 關於繫結變數變數
- 關於繫結變數的SQL繫結什麼值變數SQL
- 繫結變數的一個例子變數
- 繫結變數的使用範圍變數
- oracle繫結變數的測試Oracle變數
- 查詢繫結變數的值變數
- 關於繫結變數的使用變數
- 【優化】使用繫結變數 OR 不使用繫結變數，這不是問題！優化變數
- ORACLE 繫結變數用法總結Oracle變數
- 使用繫結變數的一點總結！變數
- 【最佳化】使用繫結變數 OR 不使用繫結變數，這不是問題！變數
- Oracle 變數繫結與變數窺視合集Oracle變數
- Oracle 繫結變數窺探Oracle變數
- oracle 繫結變數（bind variable）Oracle變數
- 如何獲取繫結變數變數
- Oracle 繫結變數 詳解Oracle變數
- 關於DSS中的繫結變數變數
- 檢視未繫結變數的sql變數SQL
- 獲取sql繫結變數的值SQL變數