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 /*+rule*/ 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，該問題到此已得到有效處理。

4.MOS中的參考資訊
關於這個問題Oracle的MOS中[ID 330239.1]文章有專門的描述，引用在此，供參考。
Memory Notification: Library Cache Object Loaded Into Sga [ID 330239.1]

Applies to:
Oracle Server - Enterprise Edition
This problem can occur on any platform.
Oracle Server Enterprise Edition
.
Symptoms

The following messages are reported in alert.log after 10g Release 2 is installed.

        Memory Notification: Library Cache Object loaded into SGA
        Heap size 2294K exceeds notification threshold (2048K)
Changes

Installed / Upgraded to 10g Release 2
Cause

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.
Solution

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

SQL> show parameter _kgl_large_heap_warning_threshold
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
_kgl_large_heap_warning_threshold integer 8388608

If using an "old-style" init parameter,

Edit the init parameter file and add

_kgl_large_heap_warning_threshold=8388608

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.

5.小結
在日常維護資料庫的過程中，需要密切關注alert警告檔案中出現的任何異常動態，及時處理，將問題化解在萌芽階段。

Good luck.

secooler
10.04.16

-- The End --

【問題處理】Memory Notification: Library Cache Object loaded into SGA

相關文章