Monday, July 20, 2009

Memory Notification: Library Cache Object loaded into SGA

Often you will see below error from alert log in Oracle Database 10g.
Memory Notification: Library Cache Object loaded into SGA
Heap size 2135K exceeds notification threshold (2048K)
Details in trace file

These warning messages is indicated as a Notification in alert messages and should not cause the session to fail. They appear as a result of new event messaging mechanism and memory manager in 10g Release 2.

This means that the process is just spending a lot of time in finding free memory extents during an allocation 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.

In 10.2.0.1 set the new undocumented parameter, the KGL heap size warning 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.

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

SQL> shutdown immediate SQL> startup

SQL> show parameter _kgl
NAME TYPE VALUE
--------------------------------- ------- ----------------
_kgl_large_heap_warning_threshold integer 8388608



This messages does not appear in version 10.2.0.2 or later, so upgrade to the latest version is also another solution to this.

For details, refer to metalink document, Doc ID: 330239.1