[20121016]字符串长度与绑定变量的子光标.txt
在使用绑定变量时,字符串长度变化会导致产生子光标.如果绑定变量中varchar2(4000)的变量越过,变数也可能变大.这些东西
在许多大师的blog都提到,自己拿人家的例子做一些测试.
SQL> select * from v$version ;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
create table t (a varchar2(4000));
SQL> alter system flush shared_pool;
System altered.
SQL> select sql_text,sql_id,child_number,executions,parse_calls,loads,invalidations from v$sql where sql_text like '%findme%' and sql_text like 'INSERT%';
no rows selected
2.开始测试:
declare
instring varchar2(4000);
begin
for i in 1..2001 loop
instring := rpad('X',i,'X');
insert /*+ findme */ into t values (instring);
end loop;
end;
/
commit;
SQL> select sql_text,sql_id,child_number,executions,parse_calls,loads,invalidations from v$sql where sql_text like '%findme%' and sql_text like 'INSERT%';
SQL_TEXT SQL_ID CHILD_NUMBER EXECUTIONS PARSE_CALLS LOADS INVALIDATIONS
-------------------------------------------------------- ------------- ------------ ---------- ----------- ---------- -------------
INSERT /*+ findme */ INTO T VALUES (:B1 ) 9ptp3f0fqgryt 0 32 1 1 0
INSERT /*+ findme */ INTO T VALUES (:B1 ) 9ptp3f0fqgryt 1 96 0 1 0
INSERT /*+ findme */ INTO T VALUES (:B1 ) 9ptp3f0fqgryt 2 1872 0 1 0
INSERT /*+ findme */ INTO T VALUES (:B1 ) 9ptp3f0fqgryt 3 1 0 1 0
SQL> @share 9ptp3f0fqgryt
old 15: and q.sql_id like ''&1''',
new 15: and q.sql_id like ''9ptp3f0fqgryt''',
SQL_TEXT = INSERT /*+ findme */ INTO T VALUES (:B1 )
SQL_ID = 9ptp3f0fqgryt
ADDRESS = 00000000DEB73608
CHILD_ADDRESS = 00000000DEAF8330
CHILD_NUMBER = 0
--------------------------------------------------
SQL_TEXT = INSERT /*+ findme */ INTO T VALUES (:B1 )
SQL_ID = 9ptp3f0fqgryt
ADDRESS = 00000000DEB73608
CHILD_ADDRESS = 00000000DEB4CCF8
CHILD_NUMBER = 1
BIND_LENGTH_UPGRADEABLE = Y
--------------------------------------------------
SQL_TEXT = INSERT /*+ findme */ INTO T VALUES (:B1 )
SQL_ID = 9ptp3f0fqgryt
ADDRESS = 00000000DEB73608
CHILD_ADDRESS = 00000000DEADF790
CHILD_NUMBER = 2
BIND_LENGTH_UPGRADEABLE = Y
--------------------------------------------------
SQL_TEXT = INSERT /*+ findme */ INTO T VALUES (:B1 )
SQL_ID = 9ptp3f0fqgryt
ADDRESS = 00000000DEB73608
CHILD_ADDRESS = 00000000DEB11DE8
CHILD_NUMBER = 3
BIND_LENGTH_UPGRADEABLE = Y
--------------------------------------------------
PL/SQL procedure successfully completed.
从执行测试可以推出:
产生子光标的原因是BIND_LENGTH_UPGRADEABLE.
字符串长度从1~32的child_number=0
字符串长度从33~128的child_number=1
字符串长度从129~2000的child_number=2
字符串长度从2001~的child_number=3
修改存贮过程执行看看.使用reverse翻转,从字符串长的那头开始插入:
declare
instring varchar2(4000);
begin
for i in reverse 1..2001 loop
instring := rpad('X',i,'X');
insert /*+ findme */ into t values (instring);
end loop;
end;
/
commit;
SQL> delete from t;
2001 rows deleted.
SQL> commit ;
Commit complete.
SQL> alter system flush shared_pool;
System altered.
--再次执行存贮过程.
SQL> select sql_text,sql_id,child_number,executions,parse_calls,loads,invalidations from v$sql where sql_text like '%findme%' and sql_text like 'INSERT%';
SQL_TEXT SQL_ID CHILD_NUMBER EXECUTIONS PARSE_CALLS LOADS INVALIDATIONS
-------------------------------------------------------- ------------- ------------ ---------- ----------- ---------- -------------
INSERT /*+ findme */ INTO T VALUES (:B1 ) 9ptp3f0fqgryt 0 2001 1 1 0
--可以发现仅仅有1个子光标.
3.很明显无法控制程序的执行时字符串的长度,如果绑定中存在许多字段,都是varchar2(4000),这样会出现许多子光标.
declare
instring varchar2(4000);
begin
for i in 1..2001 loop
instring := rpad('X',i,'X');
instring := rpad(instring,4000); --这样尾部添加的是空格,不知道这样是否消耗许多空间.
insert /*+ findme */ into t values (trim(instring));
end loop;
end;
/
SQL> delete from t;
2001 rows deleted.
SQL> commit ;
Commit complete.
SQL> alter system flush shared_pool;
System altered.
--再次执行存贮过程.
SQL> select sql_text,sql_id,child_number,executions,parse_calls,loads,invalidations from v$sql where sql_text like '%findme%' and sql_text like 'INSERT%';
SQL_TEXT SQL_ID CHILD_NUMBER EXECUTIONS PARSE_CALLS LOADS INVALIDATIONS
-------------------------------------------------------- ------------- ------------ ---------- ----------- ---------- -------------
INSERT /*+ findme */ INTO T VALUES (TRIM(:B1 )) gsyu9rnbyxmd6 0 2001 1 1 0
--不知道还有什么方法,不过这样明显不好.
4.修改变量insting的定义看看:
declare
instring varchar2(1000);
begin
for i in 1..1000 loop
instring := rpad('X',i,'X');
insert /*+ findme */ into t values (instring);
end loop;
end;
/
commit;
SQL> delete from t;
2001 rows deleted.
SQL> commit ;
Commit complete.
SQL> alter system flush shared_pool;
System altered.
--再次执行存贮过程.
SQL> select sql_text,sql_id,child_number,executions,parse_calls,loads,invalidations from v$sql where sql_text like '%findme%' and sql_text like 'INSERT%';
SQL_TEXT SQL_ID CHILD_NUMBER EXECUTIONS PARSE_CALLS LOADS INVALIDATIONS
-------------------------------------------------------- ------------- ------------ ---------- ----------- ---------- -------------
INSERT /*+ findme */ INTO T VALUES (:B1 ) 9ptp3f0fqgryt 0 1000 1 2 1
--奇怪,修改为instring varchar2(1000),为什么就没有产生子光标呢?
--再改!
declare
instring varchar2(1001);
begin
for i in 1..1001 loop
instring := rpad('X',i,'X');
insert /*+ findme */ into t values (instring);
end loop;
end;
/
commit;
SQL> select sql_text,sql_id,child_number,executions,parse_calls,loads,invalidations from v$sql where sql_text like '%findme%' and sql_text like 'INSERT%';
SQL_TEXT SQL_ID CHILD_NUMBER EXECUTIONS PARSE_CALLS LOADS INVALIDATIONS
-------------------------------------------------------- ------------- ------------ ---------- ----------- ---------- -------------
INSERT /*+ findme */ INTO T VALUES (:B1 ) 9ptp3f0fqgryt 0 32 1 1 0
INSERT /*+ findme */ INTO T VALUES (:B1 ) 9ptp3f0fqgryt 1 96 0 1 0
INSERT /*+ findme */ INTO T VALUES (:B1 ) 9ptp3f0fqgryt 2 873 0 1 0
--看来定义表变量选择合适就可以,不一定都是varchar2(4000).
--许多东西不懂,感觉1000是一道坎.