[20121016]字符串长度与绑定变量的子光标.txt

[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是一道坎.

上一篇:freetype安装使用详解(矢量字体操作)


下一篇:带你读《OpenCV 4计算机视觉项目实战 (原书第2版)》之一:OpenCV入门