Memory Notification: Library Cache Object loaded into SGA

0.环境

--系统环境
[oracle@bas bdump]$ more /etc/redhat-release 
Red Hat Enterprise Linux AS release 4 (Nahant Update 7)

--数据库版本
SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bi
PL/SQL Release 10.2.0.1.0 - Production
CORE    10.2.0.1.0      Production
TNS for Linux: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

1.alert日志信息

Sun Dec 18 02:03:38 2011
Memory Notification: Library Cache Object loaded into SGA
Heap size 7607K exceeds notification threshold (2048K)
Details in trace file /opt/app/oracle/admin/BAS/udump/bas_ora_29900.trc

2.bas_ora_29900.trc文件信息

[oracle@bas bdump]$ more  /opt/app/oracle/admin/BAS/udump/bas_ora_29900.trc
/opt/app/oracle/admin/BAS/udump/bas_ora_29900.trc
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
ORACLE_HOME = /opt/app/oracle/product/10.2.0/db_1
System name:    Linux
Node name:      bas
Release:        2.6.9-78.ELsmp
Version:        #1 SMP Wed Jul 9 15:46:26 EDT 2008
Machine:        x86_64
Instance name: BAS
Redo thread mounted by this instance: 1
Oracle process number: 34
Unix process pid: 29900, image: oracle@bas (TNS V1-V3)

*** 2011-12-18 02:03:35.244
*** SERVICE NAME:(SYS$USERS) 2011-12-18 02:03:35.229
*** SESSION ID:(5465.1) 2011-12-18 02:03:35.229
Memory Notification: Library Cache Object loaded into SGA
Heap size 5249K exceeds notification threshold (2048K)
LIBRARY OBJECT HANDLE: handle=25d38a9d8 mutex=0x25d38ab08(0)

3.警告原因

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. 

从这里可以看出来，这个只是分配大的内存块(超过_kgl_large_heap_warning_threshold参数值)的一个警告信息，不会对系统的性能以及ORA-4031产生什么影响，如果不是很在意这个警告，可以忽略

4.解决方法

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 

If using an "old-style" init parameter, 

Edit the init parameter file and add 

_kgl_large_heap_warning_threshold=8388608

NOTE:  
1)The default threshold in 10.2.0.1 is 2M.   So these messages could show up frequently in some application environments.
2)In 10.2.0.2,  the threshold was increased to 50MB after regression tests, so this should be a reasonable and recommended value. 

参考MOS:330239.1