一個sql最多可擁有多少子游標

myownstars發表於2012-10-26

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/,如需轉載,請註明出處,否則將追究法律責任。

相關文章