[20160224]繫結變數的分配長度.txt
[20160224]繫結變數的分配長度.txt
--如果繫結變數中字串分配佔用空間的長度變化，oracle會建立子游標。
--昨天被別人問一個問題，透過例子來說明：
1.環境：
SCOTT@book> @ &r/ver1
PORT_STRING VERSION BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
SCOTT@book> create table t (c1 varchar2(4000));
Table created.
2.建立指令碼：
--這個測試指令碼來自tom。
declare
v_c1 varchar2(4000);
begin
for i in 1..4000 loop
v_c1 := rpad('X',i);
execute immediate 'insert into t values (:instring) ' using v_c1 ;
end loop;
end;
/
commit;
--找到sql_id ,過程略。sql_id=''7v2jg1nahvkzn'.
SCOTT@book> select sql_id, child_number, executions from v$sql where sql_id = '7v2jg1nahvkzn';
SQL_ID CHILD_NUMBER EXECUTIONS
------------- ------------ ----------
7v2jg1nahvkzn 0 32
7v2jg1nahvkzn 1 96
7v2jg1nahvkzn 2 1872
7v2jg1nahvkzn 3 2000
SCOTT@book> @ &r/share 7v2jg1nahvkzn
SQL_TEXT = insert into t values (:instring)
SQL_ID = 7v2jg1nahvkzn
ADDRESS = 0000000063C72778
CHILD_ADDRESS = 00000000677CDED8
CHILD_NUMBER = 0
REASON = <ChildNode><ChildNumber>0</ChildNumber><ID>40</ID><reason>Bind
mismatch(22)</reason><size>4x4</size><bind_position>0</bind_position><original_oacflg>19</original_oacflg><original_oacmxl>32</original_oacmxl><upgradeable_new_oacmxl>128</upgradeable_new_oacmxl></ChildNode>
--------------------------------------------------
SQL_TEXT = insert into t values (:instring)
SQL_ID = 7v2jg1nahvkzn
ADDRESS = 0000000063C72778
CHILD_ADDRESS = 0000000062245EE8
CHILD_NUMBER = 1
BIND_LENGTH_UPGRADEABLE = Y
REASON = <ChildNode><ChildNumber>1</ChildNumber><ID>40</ID><reason>Bind
mismatch(22)</reason><size>4x4</size><bind_position>0</bind_position><original_oacflg>19</original_oacflg><original_oacmxl>128</original_oacmxl><upgradeable_new_oacmxl>32</upgradeable_new_oacmxl></ChildNode>
--------------------------------------------------
SQL_TEXT = insert into t values (:instring)
SQL_ID = 7v2jg1nahvkzn
ADDRESS = 0000000063C72778
CHILD_ADDRESS = 00000000850FF490
CHILD_NUMBER = 2
BIND_LENGTH_UPGRADEABLE = Y
REASON = <ChildNode><ChildNumber>2</ChildNumber><ID>40</ID><reason>Bind
mismatch(22)</reason><size>4x4</size><bind_position>0</bind_position><original_oacflg>19</original_oacflg><original_oacmxl>2000</original_oacmxl><upgradeable_new_oacmxl>4000</upgradeable_new_oacmxl></ChildNode>
--------------------------------------------------
SQL_TEXT = insert into t values (:instring)
SQL_ID = 7v2jg1nahvkzn
ADDRESS = 0000000063C72778
CHILD_ADDRESS = 000000006B315AD8
CHILD_NUMBER = 3
BIND_LENGTH_UPGRADEABLE = Y
REASON = <ChildNode><ChildNumber>3</ChildNumber><ID>40</ID><reason>Bind
mismatch(22)</reason><size>4x4</size><bind_position>0</bind_position><original_oacflg>19</original_oacflg><original_oacmxl>4000</original_oacmxl><upgradeable_new_oacmxl>2000</upgradeable_new_oacmxl></ChildNode>
--------------------------------------------------
PL/SQL procedure successfully completed.
--很明顯，不能使用同一個游標的原因是BIND_LENGTH_UPGRADEABLE.
--字串長度變化是32，32+96=128,32+96+1872=2000.也就是分4個段 1-32,33-128,129-2000,2001-4000.
3.繼續測試：
--我前面的測試字串長度從小到大。如果反過來呢?
--指令碼修改如下
SCOTT@book> alter system flush shared_pool;
System altered.
declare
v_c1 varchar2(4000);
begin
for i in REVERSE 1..4000 loop
v_c1 := rpad('X',i);
execute immediate 'insert into t (c1) values (:instring) ' using v_c1 ;
end loop;
end;
/
commit;
--為了避免前面的sql語句，我對sql語句進行了修改。另外for迴圈使用了REVERSE。也就是先插入字串最大的情況。
--確定sql_id='0v70rn71pdtcj'.
SCOTT@book> select sql_id, child_number, executions from v$sql where sql_id = '0v70rn71pdtcj';
SQL_ID CHILD_NUMBER EXECUTIONS
------------- ------------ ----------
0v70rn71pdtcj 0 4000
--很明顯這個時候僅僅1個子游標。因為一開始分很大的空間對於V_C1變數。
4.但是如果改用select呢？
declare
v_c1 varchar2(4000);
begin
for i in 1..4000 loop
v_c1 := rpad('X',i);
execute immediate 'select count(*) from t where c1=:instring' using v_c1 ;
end loop;
end;
/
commit;
--確定sql_id='9mrd273576n14'
SCOTT@book> select sql_id, child_number, executions from v$sql where sql_id = '9mrd273576n14';
SQL_ID CHILD_NUMBER EXECUTIONS
------------- ------------ ----------
9mrd273576n14 0 32
9mrd273576n14 1 96
9mrd273576n14 2 1872
9mrd273576n14 3 2000
SCOTT@book> @ &r/bind_cap 9mrd273576n14
C200
-----------------------------------------
select count(*) from t where c1=:instring
SQL_ID CHILD_NUMBER WAS NAME POSITION MAX_LENGTH LAST_CAPTURED DATATYPE_STRING VALUE_STRING
------------- ------------ --- -------------------- ---------- ---------- ------------------- --------------- --------------
9mrd273576n14 0 YES :INSTRING 1 32 2016-02-24 09:22:54 VARCHAR2(32) X
1 YES :INSTRING 1 128 2016-02-24 09:22:54 VARCHAR2(128) X
2 YES :INSTRING 1 2000 2016-02-24 09:22:55 VARCHAR2(2000) X
--從這個檢視也可以看出字串長度變化，沒有包括CHILD_NUMBER＝3.這個我同事問的第一個問題？為什麼沒有看到CHILD_NUMBER＝3的情況。
--很簡單受隱含引數_cursor_bind_capture_area_size大小控制。
SCOTT@book> @ &r/dpcx 9mrd273576n14 '' 3
argment : typical all advanced partition predicate remote note parallel projection alias peeked_binds outline adaptive
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 9mrd273576n14, child number 3
-------------------------------------
select count(*) from t where c1=:instring
Plan hash value: 2966233522
----------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 785 (100)| |
| 1 | SORT AGGREGATE | | 1 | 2002 | | |
|* 2 | TABLE ACCESS FULL| T | 64 | 125K| 785 (1)| 00:00:10 |
----------------------------------------------------------------------------
Peeked Binds (identified by position):
--------------------------------------
1 - (VARCHAR2(30), CSID=852, Not Captured)
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("C1"=:INSTRING)
--從這裡看出沒有capture。
SYS@book> @ &r/hide _cursor_bind_capture_area_size
NAME DESCRIPTION DEFAULT_VALUE SESSION_VALUE SYSTEM_VALUE
------------------------------ --------------------------------------------- ------------- ------------- -------------
_cursor_bind_capture_area_size maximum size of the cursor bind capture area TRUE 400 400
--因為capture area size是400，這樣0-32 是CHILD_NUMBER=0,33-128 是CHILD_NUMBER=1，129-2000 ，CHILD_NUMBER=2，這樣超出400無法在抓取。
SYS@book> alter system set "_cursor_bind_capture_area_size"=2002 scope=memory;
System altered.
SCOTT@book> alter system flush shared_pool;
System altered.
declare
v_c1 varchar2(4000);
begin
for i in 1..4000 loop
v_c1 := rpad('X',i);
execute immediate 'select count(*) from t where c1=:instring' using v_c1 ;
end loop;
end;
/
commit;
SCOTT@book> @ &r/bind_cap 9mrd273576n14
C200
-----------------------------------------
select count(*) from t where c1=:instring
SQL_ID CHILD_NUMBER WAS NAME POSITION MAX_LENGTH LAST_CAPTURED DATATYPE_STRING VALUE_STRING
------------- ------------ --- -------------------- ---------- ---------- ------------------- --------------- -------------
9mrd273576n14 0 YES :INSTRING 1 32 2016-02-24 09:41:05 VARCHAR2(32) X
1 YES :INSTRING 1 128 2016-02-24 09:41:05 VARCHAR2(128) X
2 YES :INSTRING 1 2000 2016-02-24 09:41:05 VARCHAR2(2000) X
3 YES :INSTRING 1 4000 2016-02-24 09:41:05 VARCHAR2(4000) X
--這樣第1個問題解決。
4.sqlplus測試問題：
SCOTT@book> alter system flush shared_pool;
System altered.
SCOTT@book> variable instring varchar2(4000)
SCOTT@book> exec :instring := rpad('X',1);
PL/SQL procedure successfully completed.
SCOTT@book> select count(*) from t where c1=:instring;
COUNT(*)
----------
2
SCOTT@book> exec :instring := rpad('X',33);
PL/SQL procedure successfully completed.
SCOTT@book> select count(*) from t where c1=:instring;
COUNT(*)
----------
2
SCOTT@book> select sql_id, child_number, executions from v$sql where sql_id = '9mrd273576n14';
SQL_ID CHILD_NUMBER EXECUTIONS
------------- ------------ ----------
9mrd273576n14 0 2
--為什麼在sqlplus進行類似的測試問題消失呢？如果看看繫結變數的捕獲相關檢視：
SCOTT@book> @ &r/bind_cap 9mrd273576n14
C200
-----------------------------------------
select count(*) from t where c1=:instring
SQL_ID CHILD_NUMBER WAS NAME POSITION MAX_LENGTH LAST_CAPTURED DATATYPE_STRING VALUE_STRING
------------- ------------ --- -------------------- ---------- ---------- ------------------- --------------- -----------------
9mrd273576n14 0 YES :INSTRING 1 4000 2016-02-24 09:47:59 VARCHAR2(4000) X
--可以發現在sqlplus執行，型別長度是其中定義的長度。已經是4000.這樣類似的測試就不會出現子游標問題了。
--而在plsql中雖然定義是varchar2(4000),oracle在執行時還是分成了4段，也許為了節約記憶體的需要。
--如果換成如下測試，問題就可以再現了：
SCOTT@book> variable instring varchar2(32)
SCOTT@book> exec :instring := rpad('X',1);
PL/SQL procedure successfully completed.
SCOTT@book> Select count(*) from t where c1=:instring;
COUNT(*)
----------
2
SCOTT@book> variable instring varchar2(200)
SCOTT@book> exec :instring := rpad('X',33);
PL/SQL procedure successfully completed.
SCOTT@book> Select count(*) from t where c1=:instring;
COUNT(*)
----------
2
--確定sql_id=9msm2r8u8fv55.
SCOTT@book> select sql_id, child_number, executions from v$sql where sql_id = '9msm2r8u8fv55';
SQL_ID CHILD_NUMBER EXECUTIONS
------------- ------------ ----------
9msm2r8u8fv55 0 1
1 1
SCOTT@book> @ &r/bind_cap 9msm2r8u8fv55
C200
-----------------------------------------
Select count(*) from t where c1=:instring
SQL_ID CHILD_NUMBER WAS NAME POSITION MAX_LENGTH LAST_CAPTURED DATATYPE_STRING VALUE_STRING
------------- ------------ --- -------------------- ---------- ---------- ------------------- --------------- -------------
9msm2r8u8fv55 0 YES :INSTRING 1 32 2016-02-24 09:51:40 VARCHAR2(32) X
1 YES :INSTRING 1 2000 2016-02-24 09:52:02 VARCHAR2(2000) X
--還可以看出我定義variable instring varchar2(200)，實際上在v$sql_bind_capture支援的資料型別VARCHAR2(2000)。
--oracle根據分配長度根據佔用的空間，選擇最大的32,128,2000,4000空間。
--最後附上bind_cap指令碼：
# cat bind_cap.sql
set verify off
column value_string format a50
column datatype_string format a15
break on sql_id on child_number skip 1
select replace(sql_fulltext,chr(13),'') c200 from v$sql where sql_id='&1' and rownum<=1;
SELECT sql_id,
child_number,
was_captured,
name,
position,
max_length,
last_captured,
datatype_string,
DECODE (
datatype_string,
'DATE', TO_CHAR (TO_DATE (value_string, 'mm/dd/yy hh24:mi:ss'),
'yyyy/mm/dd hh24:mi:ss'),
value_string)
value_string
FROM v$sql_bind_capture
WHERE sql_id = '&1' and was_captured='YES' and DUP_POSITION is null
order by child_number,was_captured,position;
break on sql_id on child_number skip 0
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/267265/viewspace-1993495/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- [20171021]繫結變數的分配長度8.txt變數
- [20160313]繫結變數的分配長度4.txt變數
- [20171019]繫結變數的分配長度7.txt變數
- [20160302]繫結變數的分配長度2.txt變數
- [20160307]繫結變數的分配長度3.txt變數
- [20221103]繫結變數的分配長度11.txt變數
- [20221030]繫結變數的分配長度10.txt變數
- [20161002]繫結變數的分配長度6.txt變數
- [20161001]繫結變數的分配長度5.txt變數
- [20121016]字串長度與繫結變數的子游標.txt字串變數
- 繫結變數變數
- [20170929]& 代替冒號繫結變數.txt變數
- [20160706]like % 繫結變數.txt變數
- 繫結變數之繫結變數窺探(Bind Peeking)變數
- [20171231]PLSQL使用繫結變數.txtSQL變數
- Oracle 繫結變數Oracle變數
- [20121102]PLSQL中的繫結變數.txtSQL變數
- 繫結變數的測試變數
- 繫結變數窺測的演變變數
- [20180930]in list與繫結變數個數.txt變數
- [20180912]PLSLQ與繫結變數.txt變數
- [20180930]in list與繫結變數.txt變數
- 檢視繫結變數變數
- 繫結變數窺測變數
- PLSQL使用繫結變數SQL變數
- Oracle之繫結變數Oracle變數
- 關於繫結變數變數
- 關於繫結變數的SQL繫結什麼值變數SQL
- 繫結變數的一個例子變數
- 繫結變數的使用範圍變數
- oracle繫結變數的測試Oracle變數
- 查詢繫結變數的值變數
- 關於繫結變數的使用變數
- 【優化】使用繫結變數 OR 不使用繫結變數，這不是問題！優化變數
- [20210120]in list與繫結變數個數.txt變數
- [20150812]關於抓取繫結變數.txt變數
- ORACLE 繫結變數用法總結Oracle變數
- [20191213]共享池繫結變數的值在哪裡.txt變數