Memory Notification: Library Cache Object loaded into SGA

조치방법: (다음과 같이 조치후 서버 restart)
sqlplus “/as sysdba”
alter system set "_kgl_large_heap_warning_threshold"=8388608 scope=spfile ;
shutdown immediate
startup
 
원인: (Cause 항목 참조)
해당 오류메시지는 Error 가 아닌 Warning 입니다.
이는 shared pool 영역중 libraray cache 영역의 단편화가 심해 Free Memory를 찾는데 오랜 시간이 걸릴 때
위와 같은 오류가 발생합니다.
새로운 트랜잭션이 발생시 SQL의 파싱결과 등이 저장되는 library cache영역에 단편화로 인한 메시지로
10g R2의 메모리 관리 메커니즘에 따른 메시지로 보시면됩니다.
 
 
=======================================================================
Applies to:
Oracle Server - Enterprise Edition - Version:
This problem can occur on any platform.
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.
Real memory messages should be ORA-403x when  a real memory allocation problem
occurs.
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
SQL> show parameter _kgl_large_heap_warning_threshold
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
_kgl_large_heap_warning_threshold integer 8388608
If using an "old-style" init parameter,
Edit the init parameter file and add
_kgl_large_heap_warning_threshold=8388608
In 10.2.0.2,  the threshold is increased to 50MB after regression tests, so this should be a reasonable and recommended value.
References