The patch set 220.127.116.11 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 18.104.22.168 (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 22.214.171.124 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: , , , , , , , , , , ,  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/ WaitersLists: Lock=0x2c5e4d0c0[0x2c5e4d0c0,0x2c5e4d0c0] Pin=0x2c5e4d0a0[0x2c5e4d0a0,0x2c5e4d0a0] Timestamp: Current=10-19-2012 07:02:48 HandleReference: Address=0x2c5e4d1e0 Handle=(nil) Flags= LibraryObject: Address=0x2c5e4bfc0 HeapMask=0000-0001-0001-0000 Flags=EXS Flags2= PublicFlags= 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.