[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語句.
--如果繫結變數中字串分配佔用空間的長度變化，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語句.
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/267265/viewspace-2125838/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- [20160224]繫結變數的分配長度.txt變數
- [20171021]繫結變數的分配長度8.txt變數
- [20160313]繫結變數的分配長度4.txt變數
- [20171019]繫結變數的分配長度7.txt變數
- [20160302]繫結變數的分配長度2.txt變數
- [20160307]繫結變數的分配長度3.txt變數
- [20221103]繫結變數的分配長度11.txt變數
- [20221030]繫結變數的分配長度10.txt變數
- [20161001]繫結變數的分配長度5.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變數