A short write-up on a problem faced in few newly upgraded databases to 11.2.0.1 :
I faced this issue in couple of databases which were recently upgraded to 11gR2, the problem is so severe that even connecting using “/ as sysdba” is not working and erroring out with
<span style="font-family: arial,helvetica,sans-serif; font-size: small;">ORA-04031: unable to allocate 254 bytes of shared memory ("shared pool","unknown object","PCUR^bb2f222c","kkscsAddChildNodeToContext)</span>
The SGA is sized efficiently, SGA_TARGET=8GB and shared_pool_size=900MB. Looking at the trace file generated by this error, there were 4 subpools generated and the all the 4 subpools have enough “free momory” under them.
=======================
Memory Utilization of Subpool 1
=======================
Allocation Name Size
______________ ____________
“free memory ” 413 721 528
=======================
Memory Utilization of Subpool 2
=======================
Allocation Name Size
_________ ____________
“free memory ” 369 109 200
=======================
Memory Utilization of Subpool 3
=======================
Allocation Name Size
______________ ____________
“free memory ” 1 035 124 136
“PCUR ” 4 955 061 232
=======================
Memory Utilization of Subpool 4
=======================
Allocation Name Size
______________ ____________
“free memory ” 370 023 728
We can see that all the subpools have more than 300MB of memory as free, But in the “Subpool 3” we can see some unusual amount of memory allocated to heap area “PCUR” 4 955 061 232 i.e 5GB Approx.
Now its time to find where “PCUR’ is leaking memory??
Searching in Metalink for this showed a known BUG in 11.2.0.1 and 11.2.0.2 :
<span style="font-family: arial,helvetica,sans-serif; font-size: small;">Bug 10082277 Excessive allocation in PCUR heap of "kkscsAddChildNo" (ORA-4031)</span>
As per this BUG, memory type “”kkscsAddChildNo”” under the “perm” space of “PCUR” subheap is growing over the time and is not getting deallocated leading to this situation. The solution is to apply this patch.
Oracle will be fixing this in version 12G.
There is also ora-4031 related to partition maintenance which has again made its way in 11.2.0.2. In 10g we use to see ‘mvobj part des’ consuming lot of memory. 11gR2 its ‘PRTMV” component. Patch 9578670 is available for same.
-Amit
oh.. After finding this bug in 10g, Oracle told that it will be fixed in 11g.
Now I have to check my 11.2.0.2 database where this event is enabled.
Did you already checked it in 11.2.0.2 ?
do you have a test case script for reproduce the problem ?
Joe, I think you are asking about “PRTMV”, This is explained in Metalink Doc ID 9578670.8, Which confirms its recurrence in 11.2
No test script with me as of now.. 🙁
And the bug mentioned “_kghdsidx_count”=1 as a possible workaround..