筆記:Memory Notification: Library Cache Object loaded into SGA在警告日誌中發現一些這樣的警告信息:Mon Nov 21 14:24:22 2011Memory Notification: Library Cache Object loaded ...
筆記: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.