4031 KGLH0

ORA-04031 Due To Excessive Growth In KGLH0 Heaps [ID 1351675.1]

Applies to:

Oracle Server - Enterprise Edition - Version 11.2.0.1 to 11.2.0.3 [Release 11.2]
Information in this document applies to any platform.

Symptoms

In an 11.2 database, the "KGLH0" heap seems overallocated, leading in time to an ORA-4031 error.

The trace file generated with the ORA-04031 error shows large allocations of the KGLH0 heap chunks:

===============================
Memory Utilization of Subpool 1
===============================
Allocation Name Size
___________________________ ____________
"free memory "                 101779936
"SQLA "                           390848
"KGLH0 "                       653554600  <<<
===============================
Memory Utilization of Subpool 2
===============================
Allocation Name Size
___________________________ ____________
"free memory "                  61765736
"SQLA "                        310364504
"KGLH0 "                       250430528  <<<

When witnessing a high allocation for the KGLH0 heap chunks in the trace file , a heap dump can be taken to get more detailed information:

sqlplus /nolog
connect / as sysdba
oradebug setmypid
oradebug unlimit
oradebug dump heapdump 536870914
oradebug tracefile_name
oradebug close_trace

This shows:

Heap Dump information:
.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~
---> HEAP DUMP heap name="sga heap(1,0)" desc=0x60054480
Type               Count          Sum       Average
~~~~               ~~~~~          ~~~       ~~~~~~~
freeable          338357   1255981120       3712.00
recreate           42648     81196424       1903.87
free                1524    884126976     580135.81
perm                1239    139139240     112299.63
R-freeable           137        78136        570.34
R-free                72    104715448    1454381.22
R-recreate             1      1048584    1048584.00
R-perm                 1     16729016   16729016.00
.
BreakDown
~~~~~~~~~
Type               Count          Sum       Average
~~~~               ~~~~~          ~~~       ~~~~~~~
kglhdusr            2882       238040         82.60
KGLHD              18549     10519736        567.13
KGLDA              10107      2432520        240.68
...
KGLH0^99c60e9d    275608   1128892280       4096.01    <<< ....
Free                1596    988842424 619575.45 < ....
Total = 2483014944 bytes 2424819.28k 2367.99MB
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

---> HEAP DUMP heap name="KGLH0^99c60e9d" desc=0x1072d20b0

Type               Count         Sum        Average
~~~~               ~~~~~         ~~~        ~~~~~~~
perm              275501  1101966760        3999.86  <<<<<                                                                  allocated
free              275347    15419224          56.00
freeable              63        3344          53.08
.
BreakDown
~~~~~~~~~
Type               Count         Sum        Average
~~~~               ~~~~~         ~~~        ~~~~~~~
Free              275347    15419224          56.00
kksfbc:hash1          59        2736          46.37
kgltbtab               4         608         152.00
.
Total = 1117389328 bytes 1091200.52k 1065.63MB

In this case the heap chunks of the KGLH0 heap are marked as "perm" (i.e permanent allocated chunks) and it is not visible what specific pieces of memory are allocated.

In order to get detailed information on this, set the 10235 event at level 65536 and then generate the ORA-4031 situation again:

sqlplus /nolog
connect / as sysdba
alter system set events '10235 level 65536';
exit

Then wait a while for the ORA-4031 (or at least the memory allocations) to occur, and then execute:
sqlplus /nolog
connect / as sysdba
oradebug setmypid
oradebug unlimit
oradebug dump heapdump 536870914
oradebug tracefile_name
oradebug close_trace

once done you can disable the event using

alter system set events '10235 off';

This event will add comments to each permanent memory chunk, allowing analysis which particular memory structures are allocated

A heap dump with the event set will show:

Heapdump shows:
Heap "PCUR^2181617f" desc=700000131aaff00
***********************************************
perm                  925094312
                       11086568
kksfbc:hash1                368
kgltbtab                    152

With Perm CPM comments showing:
kkscsAddChildNo       914003728
CPM trailer             3692992
...

In this case the main culprit is memory allocations of the "kkscsAddChildNo" type.

Cause

The cause of this problem has been identified in Bug:10082277. It is caused by the routine which adds child cursor diagnostics to the cursor sharing context to create a duplicate node instead of reusing exising nodes.

Solution

The issue has been fixed in the upcoming 12.1 release, as well as in the 11.2.0.3 patchset.

For other releases, apply Patch:10082277, if available for your release and platform.

The workaround is to periodically flush the shared pool.