Memory Notification: Library Cache Object loaded into SGA / ORA-600 [KGL-heap-size-exceeded] (文档 ID

APPLIES TO:

Oracle Database - Enterprise Edition - Version 10.2.0.1 and later
Information in this document applies to any platform.
***Checked for relevance 17-Aug-2014***
.

SYMPTOMS

In alert log there are reported messages like the following:

       Memory Notification: Library Cache Object loaded into SGA
       Heap size <heap size K> exceeds notification threshold (51200K)

CAUSE

These are warning messages that are not causing process failure. They appear as a result of event messaging mechanism and memory manager introduced starting with 10gR2 database release. As large objects in the shared pool can potentially cause problems this warning threshold was implemented. Items/SQLs which allocate more space than this warning threshold, outputs a warning to the alert log. This warning is only to inform that a given heap object exceeds the defined threshold size and a trace file is generated so that a DBA can check potentially expensive - from shared memory point of view - objects. These are purely warning messages and have no impact on the database functionality, although they are designed to indicate possible tuning opportunities in customers' applications. The messages do not imply that an ORA-4031 is about to happen immediately unless the size of shared pool is very small.

SOLUTION

A hidden parameter - _kgl_large_heap_warning_threshold - that sets the KGL heap size warning threshold was introduced starting with 10gR2. Warnings are written if heap size in shared pool exceeds this threshold: 

_kgl_large_heap_warning_threshold => maximum heap size before KGL writes warnings to the alert log

Besides reducing the heap size from the application code (recommended) one can set _kgl_large_heap_warning_threshold to a reasonable high value or zero to prevent these warning messages. The value needs to be set in bytes. For example:

If using a SPFILE: 
=============
(logged in as "/ as sysdba")

SQL> alter system set "_kgl_large_heap_warning_threshold"=83886080 scope=spfile ; 

SQL> shutdown immediate

SQL> startup

If using a PFILE:

============
Edit the PFILE and add:

_kgl_large_heap_warning_threshold=8388608

The default threshold in 10.2.0.1 is only 2M. Starting with 10.2.0.2 the threshold was increased to 50MB after regression tests, so this should be a reasonable and recommended value in most cases.

In 12.1.0.2 database release (that includes the fix Bug 15898589 - enhancement to restrict the size of SGA base library cache heaps) an enhancement to restrict the size of SGA base library cache heaps was introduced in order to avoid running out of space inside shared pool and hence ORA-4031 errors. With this fix, a new hidden parameter - _kgl_large_heap_assert_threshold - was also introduced. 

_kgl_large_heap_assert_threshold => maximum heap size before KGL raises an internal error

Its value represents the maximum heap size before raising the ORA-600 internal error like:

ORA-00600: internal error code, arguments: [KGL-heap-size-exceeded], [0x7FF91F844240], [6], [532279608], [], [], [], [], [], [], [], []

To check current value of the parameters, one can run the following query:

select
nam.ksppinm NAME,
nam.ksppdesc DESCRIPTION,
val.KSPPSTVL
from
x$ksppi nam,
x$ksppsv val
where nam.indx = val.indx and nam.ksppinm like '%kgl_large_heap_%_threshold%';

For example in 12.1.0.2 the default values for the two parameters are:

SQL> select
2 nam.ksppinm NAME,
3 nam.ksppdesc DESCRIPTION,
4 val.KSPPSTVL
5 from
6 x$ksppi nam,
7 x$ksppsv val
8 where nam.indx = val.indx and nam.ksppinm like '%kgl_large_heap_%_threshold%';

NAME
--------------------------------------------------------------------------------
DESCRIPTION
--------------------------------------------------------------------------------
KSPPSTVL
--------------------------------------------------------------------------------
_kgl_large_heap_warning_threshold
maximum heap size before KGL writes warnings to the alert log
52428800

 

_kgl_large_heap_assert_threshold
maximum heap size before KGL raises an internal error
524288000

Please be aware that by setting _kgl_large_heap_warning_threshold to 0 in 12.1.0.2 it is caused the problem described in:

Bug 22330282 - "Heap size 0K exceeds notification threshold" alert messages when "_kgl_large_heap_warning_threshold" is set to 0 (Document: 22330282.8)

To fix this problem either:

==> Apply Patch 22330282 or open a backport request with Oracle Support if the patch is not available for your database release and/or platform.

or

==> Either of following options may workaround the issue:
a. Set _kgl_large_heap_warning_threshold to a very large value.
b. Set both _kgl_large_heap_warning_threshold and _kgl_large_heap_assert_threshold to zero.

NOTE: In 11.2.0.4 or 12.1.0.1 in order to have the _kgl_large_heap_assert_threshold parameter available and avoid the Bug 22330282, apply Patch Bug 22330282 or open a backport request with Oracle Support if the patch is not available for your database release and/or platform.