[20160302]绑定变量的分配长度2.txt
--如果绑定变量中字符串分配占用空间的长度变化，oracle会建立子光标。
--参考连接：
http://blog.itpub.net/267265/viewspace-1993495/
--oracle 可以通过一个10503事件设置大的缓存，测试看看：
$ oerr ora 10503
10503, 00000, "enable user-specified graduated bind lengths"
// *Cause:
// *Action:
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
create table t (c1 varchar2(4000));
-- ALTER SESSION SET EVENTS '10503 trace name context level <buffer length>, forever';
ALTER SESSION SET EVENTS '10503 trace name context level 2000, forever';
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;
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 2000
9mrd273576n14 1 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 2000 2016-03-02 15:39:23 VARCHAR2(2000) X
1 YES :INSTRING 1 4000 2016-03-02 15:39:23 VARCHAR2(4000) X
--可以发现这样仅仅存在2个光标。
SCOTT@book> alter system flush shared_pool;
System altered.
SCOTT@book> ALTER SESSION SET EVENTS '10503 trace name context off';
Session altered.
--重新执行看看。
--也可以退出再登录执行看看。
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
--这种情况仅仅存在这种因素产生大量子光标的情况下可以考虑这种方式。