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.
Prior to 11.2.0.2, the limit for # of child cursors per parent was 32768 and the ORA-00600 argument was [17059]. But with a of fix for bug# 8946311 (included in 11.2.0.2), this limit was increased to 65536. Thanks for sharing that in 11.2.0.3 code path has changed.
Hi Saurabh
Thank you for pointing out bug# 8946311.
What IMO is incredible in this area is that up to 11.1.0.6 the parents with many children (more than 1026, the previous limit) don’t cause any problem. In fact, the parents are obsoleted and new ones are created. Then, in 11.1.0.7, they increased the maximum number but removed the code that correctly handles what happens when it is exceeded. As a “solution”, in 11.2.0.3, they strongly decreased the maximum number to a value way below the ones we were used to. Quite a mess…
Best,
Chris