筆記：Memory Notification: Library Cache Object loaded into SGA
在警告日志中發現一些這樣的警告信息：
Mon Nov 21 14:24:22 2011
Memory Notification: Library Cache Object loaded into SGA
Heap size 5800K exceeds notification threshold (2048K)
Details in trace file c:\oracle\product\10.2.0\admin\hy2003\udump\hy2003_ora_4372.trc
KGL object name :PCDM.BIN$C8iYfZ9TS8ORv9KUD+hrSA==$0
在網上查到：
【問題處理】Memory Notification: Library Cache Object loaded into SGA
1.問題現象
數據庫日常巡檢過程中，在alert日志中發現如下警告信息
……省略……
Thu Apr 15 22:06:31 2010
Memory Notification: Library Cache Object loaded into SGA
Heap size 3215K exceeds notification threshold (2048K)
KGL object name :SELECT TOWNER, TNAME, NAME, LENGTH, PRECISION, SCALE, TYPE, ISNULL, CONNAME, COLID, INTCOLID, SEGCOLID, COMMENT$, DEFAULT$, DFLTLEN, ENABLED, DEFER, FLAGS, COLPROP, ADTNAME, ADTOWNER, CHARSETID, CHARSETFORM, FSPRECISION, LFPRECISION, CHARLEN, TFLAGS, TYPESYN, COLCLASS FROM SYS.EXU10COE WHERE TOBJID = :1 ORDER BY COLCLASS
Thu Apr 15 22:06:55 2010
Memory Notification: Library Cache Object loaded into SGA
Heap size 5118K exceeds notification threshold (2048K)
Details in trace file /home/oracle/oracle/product/10.2.0/db_1/admin/orcl/udump/orcl_ora_18031.trc
KGL object name :SELECT SYS_XMLGEN(VALUE(KU$), XMLFORMAT.createFormat2('TABLE_T', '7')), KU$.OBJ_NUM FROM SYS.KU$_FHTABLE_VIEW KU$ WHERE NOT (BITAND (KU$.PROPERTY,8192)=8192) AND NOT BITAND(KU$.SCHEMA_OBJ.FLAGS,128)!=0 AND KU$.SCHEMA_OBJ.NAME=:NAME1 AND KU$.SCHEMA_OBJ.OWNER_NAME=:SCHEMA2
Fri Apr 16 05:00:07 2010
……省略……
2.問題原因
在Oracle 10.2.0.1版本數據庫中隱含參數_kgl_large_heap_warning_threshold默認值是2M，該參數控制加載到內存中對象的大小，當加載的對象大於2M時，就會在alert警告文件中進行提示。2M的默認大小相對太小，因此在10.2.0.1版本中可能很容易遇到這個報錯信息。該參數默認值在10.2.0.2版本中進行了調整，調整到了50M。
1）確認出現警告的數據庫的版本是10.2.0.1
sys@orcl> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
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
2）確認隱含參數_kgl_large_heap_warning_threshold的默認大小
sys@orcl> select a.ksppinm name,b.ksppstvl value,a.ksppdesc description
2 from x$ksppi a,x$ksppcv b
3 where a.indx = b.indx
4 and a.ksppinm = '_kgl_large_heap_warning_threshold'
5 /
NAME VALUE DESCRIPTION
--------------------------------- -------- --------------------------------------------------------------
_kgl_large_heap_warning_threshold 2097152 maximum heap size before KGL writes warnings to the alert log
sys@orcl> select 2097152/1024/1024 MB from dual;
MB
----------
2
3.問題處理方法
既然知道了問題原因，處理起來就很簡單了。如果不希望在alert文件中看到這些報錯，可以適當調大隱含參數“_kgl_large_heap_warning_threshold”的值，或將其設置為“0”。
1）將_kgl_large_heap_warning_threshold參數大小調整為50M
sys@orcl> alter system set "_kgl_large_heap_warning_threshold"=52428800 scope=spfile;
System altered.
2）重啟數據庫
OK，該問題到此已得到有效處理。
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 2097152 maximum heap size before KGL writes warnings to the alert log
按介紹修改參數：
System altered alter system set '_kgl_large_heap_warning_threshold'=8388608 scope=spfile ;
重啟數據庫。
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, an SR may be in order to investigate if you are encountering a bug in the Shared Pool.
