Memory Notification: Library Cache Object loaded into SGA

    今天檢視一臺測試機上面的alert.log日誌，發現一個從來沒有遇到的問題：
Fri Mar 13 14:14:52 2009
Memory Notification: Library Cache Object loaded into SGA
Heap size 2230K exceeds notification threshold (2048K)
KGL object name :SELECT SYNNAM, DBMS_JAVA.LONGNAME(SYNNAM), DBMS_JAVA.LONGNAME(SYNTAB),             TABOWN, TABNODE, PUBLIC$, SYNOWN, SYNOWNID, TABOWNID, SYNOBJNO      FROM   SYS.EXU9PTS      WHERE  SYNOBJNO IN (                 SELECT SYNOBJNO                 FROM SYS.EXU9TYPTU                 WHERE TABOBJNO = :1 )      ORDER  BY SYNTIME
Fri Mar 13 14:14:52 2009
Memory Notification: Library Cache Object loaded into SGA
Heap size 2099K exceeds notification threshold (2048K)

原因：
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.  

解決方法：
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. This parameter was not present in 10gR1. 
1.檢視隱含引數當前值：
select name  ,value ,description 
from ( --GV$SYSTEM_PARAMETER 
select x.inst_id as instance 
,x.indx+1 
,ksppinm as name 
,ksppity 
,ksppstvl as value 
,ksppstdf as isdefault 
,decode(bitand(ksppiflg/256,1),1,'TRUE','FALSE') as ISEM 
,decode(bitand(ksppiflg/65536,3), 
1,'IMMEDIATE',2,'DEFERRED','FALSE') as ISYM 
,decode(bitand(ksppstvf,7),1,'MODIFIED','FALSE') as IMOD 
,decode(bitand(ksppstvf,2),2,'TRUE','FALSE') as IADJ 
,ksppdesc as description 
from x$ksppi x 
,x$ksppsv y 
where x.indx = y.indx 
and  ksppinm = '_kgl_large_heap_warning_threshold'
and x.inst_id = USERENV('Instance') 
) 
order by name;
version：10.2.0.1.0
name：_kgl_large_heap_warning_threshold 
value：2097152  
desc：maximum heap size before KGL writes warnings to the alert log
2.修改隱含引數值，加大值
SQL> alter system set "_kgl_large_heap_warning_threshold"=8388608 scope=spfile;
3.重啟db，使引數生效
SQL> shutdown immediate
SQL> startup

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.   If you continue to see the these warning messages in the alert log after applying 10.2.0.2 or higher, please contact Oracle Support Services for further investigation of any possible shared pool problems.