一个sql最多可拥有多少子游标

http://www.oaktable.net/content/does-parse-time-increase-linearly-number-child-cursors
由大名鼎鼎的Antognini测试所得，即sql编译时间会随着子游标数量的增多而线性增长，这个很好理解，子游标采用链表结构每次解析时必须挨个遍历才能得出最终值
到了11203，情况发生了些许变化，引入隐含参数_cursor_obsolete_threshold(默认值100)，即每个父游标最多只能有100个子游标，如果超出则创建一个新的父游标
http://www.antognini.ch/2012/10/how-many-children-can-a-parent-cursor-have-1000000/
作者采用以下脚本验证Pre-11203的版本中一个父游标最多可以拥有多少子游标
SQL> CREATE TABLE t (n NUMBER);

Table created.

SQL> INSERT INTO t VALUES (1);

1 row created.

SQL> COMMIT;

Commit complete.

SQL> execute dbms_stats.gather_table_stats(user,'t')

PL/SQL procedure successfully completed.

SQL> DECLARE
  2    l_count PLS_INTEGER;
  3  BEGIN
  4    FOR oic IN 1..100
  5    LOOP
  6      EXECUTE IMMEDIATE 'ALTER SESSION SET optimizer_index_caching = '||oic;
  7      FOR oica IN 1..10000
  8      LOOP
  9        EXECUTE IMMEDIATE 'ALTER SESSION SET optimizer_index_cost_adj = '||oica;
 10        EXECUTE IMMEDIATE 'SELECT count(*) FROM t' into l_count;
 11      END LOOP;
 12    END LOOP;
 13  END;
 14  /
DECLARE
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 6809
Session ID: 7 Serial number: 9
Alert log出现如下信息
Errors in file /u00/app/oracle/diag/rdbms/dbm11202/DBM11202/trace/DBM11202_ora_6809.trc  (incident=25425):
ORA-00600: internal error code, arguments: [15206], [], [], [], [], [], [], [], [], [], [], []
Incident details in: /u00/app/oracle/diag/rdbms/dbm11202/DBM11202/incident/incdir_25425/DBM11202_ora_6809_i25425.trc

查看跟踪文件
LibraryHandle:  Address=0x2c5e4d030 Hash=3a56fe71 LockMode=N PinMode=0 LoadLockMode=0 Status=VALD
        ObjectName:  Name=SELECT count(*) FROM t
          FullHashValue=6d0bd54734bada875cc6ce3e3a56fe71 Namespace=SQL AREA(00) Type=CURSOR(00) Identifier=978779761 wnerIdn=36
        Statistics:  InvalidationCount=0 ExecutionCount=65535 LoadCount=65536 ActiveLocks=51 TotalLockCount=65536 TotalPinCount=1
        Counters:  BrokenCount=1 RevocablePointer=1 KeepDependency=65535 BucketInUse=0 HandleInUse=0 HandleReferenceCount=0
        Concurrency:  DependencyMutex=0x2c5e4d0e0(0, 4097, 0, 0) Mutex=0x2c5e4d160(0, 5120057, 12919, 0)
        Flags=RON/PIN/TIM/PN0/DBN/[10012841]
        WaitersLists:
          Lock=0x2c5e4d0c0[0x2c5e4d0c0,0x2c5e4d0c0]
          Pin=0x2c5e4d0a0[0x2c5e4d0a0,0x2c5e4d0a0]
        Timestamp:  Current=10-19-2012 07:02:48
        HandleReference:  Address=0x2c5e4d1e0 Handle=(nil) Flags=[00]
        LibraryObject:  Address=0x2c5e4bfc0 HeapMask=0000-0001-0001-0000 Flags=EXS[0000] Flags2=[0000] PublicFlags=[0000]
          ChildTable:  size='65536'
            Child:  id='0' Table=0x2c5e4ce70 Reference=0x2c5e4c898 Handle=0x2c5e4bbd0
            Child:  id='1' Table=0x2c5e4ce70 Reference=0x2c5e4cd58 Handle=0x2c5dd4338
            Child:  id='2' Table=0x2c5e4ce70 Reference=0x2c5dce0d0 Handle=0x2c5dcdae8
            Child:  id='3' Table=0x2c5e4ce70 Reference=0x2c5dce568 Handle=0x2c5dc8298
            Child:  id='4' Table=0x2c5e4ce70 Reference=0x2c5dcea00 Handle=0x2c5dc2a48
            …
            Child:  id='65530' Table=0x2846e56d8 Reference=0x2846b07e8 Handle=0x2846b03d8
            Child:  id='65531' Table=0x2846e56d8 Reference=0x2846b0c80 Handle=0x2846ad188
            Child:  id='65532' Table=0x2846e56d8 Reference=0x2846b1118 Handle=0x2846a9f38
            Child:  id='65533' Table=0x2846e56d8 Reference=0x2846a60f8 Handle=0x2846a5ce8
            Child:  id='65534' Table=0x2846e56d8 Reference=0x2846a6590 Handle=0x2846a2a98

可看出11202中单个父游标对应的子游标最多可有65534个

 

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

转载于:http://blog.itpub.net/15480802/viewspace-747306/