pat98's always & forever

10g R2 로 upgrade 하거나 새로 설치하면 나타나는 증상인데 (SQL문 실행하면 alertSID.log 화일에 계속 기록됨) heap size 기본셋팅 사이즈때문에 나타나는 warning message 이므로 무시하거나, 신경쓰이시는 분들은 아래와 같이 조치하면 됨
================================================================================================

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