The patch set 11.2.0.3 includes a fix for bug# 10187168 which, in reality, is an enhancement request. Its purpose is to artificially limit the number of child cursors that a parent cursor can have. The concept is quite easy: when a parent cursor reaches _cursor_obsolete_threshold (default value is 100) child cursors the parent cursor is obsoleted and, as a result, a new one is created.

So, as of 11.2.0.3 (or with some PSUs and bundle patches), the answer to the question is: 100.

But how many child cursors are supported in previous 11.2 releases? This is a question that crossed my mind few weeks ago while preparing for the “Shareable Cursors” presentation that I gave at the OakTable World in San Francisco. Is it for example possible to have a parent cursor with 1,000,000 child cursors? Not that something like that is sensible, but I would like to know whether it is technically possible. In other words, whether the database engine supports it.

To try answering the question, I wrote and run on 11.2.0.2 the following test case. Notice that the PL/SQL block is able, if there is enough memory, to create 1,000,000 child cursors. Hence, before running it, I increased the shared pool size to 10GB.

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

As you can see the result is interesting

In the alert.log I found the following information:

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

As always, when I see this kind of error, I check the ORA-600/ORA-7445/ORA-700 Error Look-up Tool. Unfortunately, in this case, no information is provided. So I continued investigating the issued by looking at the trace file generated for the incident. As expected the SQL statement hitting the problem was the following:

----- Current SQL Statement for this session (sql_id=5tjqf7sx5dzmj) -----
SELECT count(*) FROM t

And the library cache content for the associated cursor was the following:

      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 OwnerIdn=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

The most interesting part is the one providing the number of child cursors: 65536.

I might be wrong but to me that means that the child number is stored as an unsigned integer taking 16 bits. And, when the maximum is exceeded, an ORA-00600 is raised.

According to this test the maximum number of child cursors for a given parent cursor is 65536. But, as already written, the fix for bug# 10187168 artificially limits it to 100.