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