1、今天检查oracle数据库时，发现alert出现这种情况Memory Notification: Library Cache Object loaded into SGA
Heap size 11226K exceeds notification threshold (10240K)
KGL object name :select fund0_.FUND_ID as FUND1_39_0_, fundapplyd1_.FUND_APPLY_DETAIL_ID as FUND1_42_1_, funditem6_.FUND_ITEM_ID as FUND1_61_2_, flexvalue2_.FLEX_VALUE_ID as FLEX1_110_3_, flexvalue3_.FLEX_VALUE_ID as FLEX1_110_4_, flexvalue4_.FLEX_VALUE_ID as FLEX1_110_5_, flexvalue5_.FLEX_VALUE_ID as FLEX1_110_6_, bankforacc7_.BAS_BANK_ID as BAS1_46_7_, bankforacc8_.BAS_BANK_ID as BAS1_46_8_, contractin9_.CONTRACT_ID as CONTRACT1_72_9_, organizati10_.ORGANIZATION_ID as ORGANIZA1_200_10_, otheraccou11_.BAS_OTHER_ID as BAS1_

在trc里面信息是这样的


*** 2012-08-29 14:17:11.666
*** SERVICE NAME:(SYS$USERS) 2012-08-29 14:17:11.660
*** SESSION ID:(143.13788) 2012-08-29 14:17:11.660
Memory Notification: Library Cache Object loaded into SGA
Heap size 11230K exceeds notification threshold (10240K)
LIBRARY OBJECT HANDLE: handle=8e970560 mutex=0x8e970690(0)
name=
select fund0_.FUND_ID as FUND1_39_0_, fundapplyd1_.FUND_APPLY_DETAIL_ID as FUND1_42_1_, funditem6_.FUND_I
TEM_ID as FUND1_61_2_, flexvalue2_.FLEX_VALUE_ID as FLEX1_110_3_, flexvalue3_.FLEX_VALUE_ID as FLEX1_110_
4_, flexvalue4_.FLEX_VALUE_ID as FLEX1_110_5_, flexvalue5_.FLEX_VALUE_ID as FLEX1_110_6_, bankforacc7_.BA
S_BANK_ID as BAS1_46_7_, bankforacc8_.BAS_BANK_ID as BAS1_46_8_, contractin9_.CONTRACT_ID as CONTRACT1_72
_9_, organizati10_.ORGANIZATION_ID as ORGANIZA1_200_10_, otheraccou11_.BAS_OTHER_ID as BAS1_

2、问题原因
在Oracle 10.2.0.1版本数据库中隐含参数_kgl_large_heap_warning_threshold默认值是2M，该参数控制加载到内存中对象的大小，当加载的对象大于2M时，就会在alert警告文件中进行提示。2M的默认大小相对太小，因此在10.2.0.1版本中可能很容易遇到这个报错信息。该参数默认值在10.2.0.2版本中进行了调整，调整到了50M。而我的这个数据库是10M，还小了。
SQL> select a.ksppinm name,b.ksppstvl value,a.ksppdesc description from x$ksppi a,x$ksppcv b where a.indx = b.indx and a.ksppinm = '_kgl_large_heap_warning_threshold';

NAME
--------------------------------------------------------------------------------
VALUE
--------------------------------------------------------------------------------
DESCRIPTION
--------------------------------------------------------------------------------
_kgl_large_heap_warning_threshold
10485760
maximum heap size before KGL writes warnings to the alert log

10485760/1024/1024
------------------
                10

 

3.问题处理方法
就是将_kgl_large_heap_warning_threshold这个差数加大到一定数值。我们日常检查oracle数据库的时候要特别细看alert信息。谢谢