Memory Notification: Library Cache Object loaded into SGA

刘泽宇

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

Gen 1 Exadata Cloud at Customer (Oracle Exadata Database Cloud Machine) - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Oracle Database Backup Service - Version N/A and later
Oracle Database Cloud Exadata Service - Version N/A and later
Oracle Database Cloud Service - Version N/A and later
Information in this document applies to any platform.

SYMPTOMS

Messages like the following are reported in the alert log:

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

       ...:KGL object name : <OBJECT_NAME>

CHANGES
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. _kgl_large_heap_warning_threshold

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 checking the memory management from the application code (recommended) you 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:

1. 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

2. If using a PFILE:

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

_kgl_large_heap_warning_threshold=83886080

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.

B. _kgl_large_heap_assert_threshold

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-00600 internal error like:

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

The default value for _kgl_large_heap_assert_threshold can be too small for objects with a large number of partitions/sub-partitions in which case the value should be increased to avoid the ORA-600 error.

For example setting:
"_kgl_large_heap_assert_threshold" = 1572864000 will raise the threshold for the assert to 1500 MB.

To check the current value of the parameters, 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 19.3.0.0 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

Before increasing the warning thresholds you can inspect the generated trace file. The ORA-00600 will generate both a trace file in the trace directory and an incident file under the incident id within the incident directory.

The top part of the trace file tells us the SQL that was being run when the error was hit:

----- Current SQL Statement for this session (sql_id=<Sql Id>) -----

This immediately shows us the SQL/ data objects that were accessed.  The KGL object name in trace file can by found by searching for 'LibraryHandle'.
After identifying the failing SQL/ object/s in isolation see/check if the memory requirement can be reduced.

A full list of known issues is given in:
    Note 1662971.1 - ORA-600 [KGL-heap-size-exceeded]
Each bug has a short description that indicates the circumstances where it is hit. The bug list can be shortened by selecting your database release to show only those issues that may affect you.

Please be aware that by setting _kgl_large_heap_warning_threshold to 0 in 12.1.0.2 exposes 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:

1. 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

2. Workaround the issue by setting:
    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.

* Before increasing the thresholds it should be taken into consideration the increase of SGA_TARGET and SHARED_POOL_SIZE by 15%, to make more free room in SGA.

NOTE 1: 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 22330282 or open a backport request with Oracle Support if the patch is not available for your database release and/or platform.

NOTE 2: The maximum value, that can be set for the 2 hidden parameters, is 2147483647 (‭1,99GB)‬. Any value beyond that limit raises the following error:
ERROR at line 1:
ORA-02017: integer value required