[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