ORA-04031 Due To Excessive Growth In KGLH0 Heaps

(Doc ID 1351675.1)

Last updated on NOVEMBER 28, 2016

Applies to:

Oracle Database - Enterprise Edition - Version 11.2.0.1 to 11.2.0.2 [Release 11.2]
Oracle Database - Enterprise Edition - Version 11.2.0.3 to 11.2.0.3 [Release 11.2]
Information in this document applies to any platform.
***Checked for relevance on 11-Nov-2014***

Symptoms

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

KGLH0 associated with the SQL Area in the Shared Pool.  Each child cursor consumes KGLH0.
Large allocation in KGLH0 can occur due to many child cursors and a lack of sharing of the parent cursor.

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

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms