Note: Alert log show Memory Notification Library Cache Object loaded into SGA

** Credit Web
ในกรณีที่เจอข้อความ object ที่ load เข้า SGA แล้ว เกิน Heap ที่กันไว้ใน SGA ถ้าไม่ได้ใช้ automatic manage memory
Memory Notification: Library Cache Object loaded into SGA
Today, I checked alert.log file. I found:
Memory Notification: Library Cache Object loaded into SGA
Heap size 9426K exceeds notification threshold (8192K)
When I found out on metalink:
These messages are report in alert.log after 10g Release 2 is installed.
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"=10485760 scope=spfile ;
SQL> shutdown immediate
SQL> startup
SQL> show parameter _kgl_large_heap_warning_threshold
NAME TYPE VALUE
--------------------------------- ------- ------------------------------
_kgl_large_heap_warning_threshold integer 10485760
If using an "old-style" init parameter,
Edit the init parameter file and add
_kgl_large_heap_warning_threshold=10485760
NOTE: The default threshold in 10.2.0.1 is 2M.
"_kgl_large_heap_warning_theshold”
SELECT a.ksppinm "Parameter",
b.ksppstvl "Session Value",
c.ksppstvl "Instance Value"
FROM x$ksppi a, x$ksppcv b, x$ksppsv c
WHERE a.indx = b.indx AND
a.indx = c.indx AND
a.ksppinm LIKE '%kgl_large_heap%';