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

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/267265/viewspace-746524/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/267265/viewspace-746524/