Memory Notification: L…

 Memory Notification: Library Cache Object loaded into SGA
 Heap size 2069K exceeds notification threshold (2048K)
 KGL object name :SELECT DISTINCT OWNER FROM ALL_OBJECTS
 Fri Oct 19 10:01:39 2012
 Memory Notification: Library Cache Object loaded into SGA
 Heap size 2259K exceeds notification threshold (2048K)
 Details in trace file /u01/oracle/admin/koaprod/udump/koaprod_ora_8467.trc
 KGL object name :SELECT NULL AS table_cat,
        o.owner AS table_schem,
        o.object_name AS table_name,
        o.object_type AS table_type,
        NULL AS remarks
   FROM all_objects o
   WHERE o.owner LIKE :1 ESCAPE '/'
     AND o.object_name LIKE :2 ESCAPE '/'
     AND o.object_type IN ('xxx', 'TABLE', 'VIEW')
   ORDER BY table_type, table_schem, table_name
 Fri Oct 19 10:01:44 2012
 Memory Notification: Library Cache Object loaded into SGA
 Heap size 3155K exceeds notification threshold (2048K)
 Details in trace file /u01/oracle/admin/koaprod/udump/koaprod_ora_8467.trc
 KGL object name :SELECT
   -- Standalone procedures and functions
   NULL AS procedure_cat,
   owner AS procedure_schem,
   object_name AS procedure_name,
   NULL,
   NULL,
   NULL,
   'Standalone procedure or function' AS remarks,
   DECODE(object_type, 'PROCEDURE', 1,
         'FUNCTION', 2,
         0) AS procedure_type
 FROM all_objects
 WHERE (object_type = 'PROCEDURE' OR object_type = 'FUNCTION')
   AND owner LIKE :1 ESCAPE '/'
   AND object_name LIKE :2 ESCAPE '/'
 UNION ALL SELECT
   -- Packaged procedure
 Fri Oct 19 10:10:33 2012
 Thread 1 advanced to log sequence 1575

 

 

 

In this Document
  Symptoms
  Changes
  Cause
  Solution

---

Applies to:

Oracle Server - Enterprise Edition - Version: 10.2.0.1 and later      [Release: and later ]
Information in this document applies to any platform.
Oracle Server Enterprise Edition
.

Symptoms

The following messages are reported in alert.log after 10g Release 2 is installed.

        Memory Notification: Library Cache Object loaded into SGA
        Heap size 2294K exceeds notification threshold (2048K)

Changes

Installed / Upgraded to 10g Release 2

Cause

These are warning messages that should not cause the program responsible for these errors to fail.  They appear as a result of new event messaging mechanism and memory manager in 10g Release 2.

The meaning is that the process is just spending a lot of time in finding free memory extents during an allocate as the memory may be heavily fragmented.  Fragmentation in memory is impossible to eliminate completely, however, continued messages of large allocations in memory indicate there are tuning opportunities on the application.  

The messages do not imply that an ORA-4031 is about to happen. 

Solution

In 10g we have a new undocumented parameter that sets the KGL heap size warning threshold.   This parameter was not present in 10gR1.  Warnings are written if heap size exceeds this threshold.
    
Set  _kgl_large_heap_warning_threshold  to a reasonable high value or zero to prevent these warning messages. Value needs to be set in bytes.

If you want to set this to 8192 (8192 * 1024) and are using an spfile:

(logged in as "/ as sysdba")

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

SQL> shutdown immediate

SQL> startup

If using an "old-style" init parameter,

Edit the init parameter file and add

_kgl_large_heap_warning_threshold=8388608

 NOTE:  The default threshold in 10.2.0.1 is 2M.   So these messages could show up frequently in some application environments.

In 10.2.0.2,  the threshold was increased to 50MB after regression tests, so this should be a reasonable and recommended value.