Memory Notification: Library Cache Object loaded into SGA

转载 2012年03月26日 12:01:01
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警告文件中出现的任何异常动态,及时处理,将问题化解在萌芽阶段。

Memory Notification: Library Cache Object loaded into SGA问题

问题:alert_SID.log出现 Memory Notification: Library Cache Object loaded into SGA Heap size 2289K excee...
  • zq9017197
  • zq9017197
  • 2012年09月07日 15:12
  • 1702

告警日志中一堆Memory Notification: Library Cache Object loaded into SGA

最近数据迁移,发现装载的数据库服务器中告警日志中
  • yangchen8497
  • yangchen8497
  • 2014年04月22日 13:47
  • 329

Memory Notification: Library Cache Object loaded into SGA

1.环境    --系统环境 [oracle@ias oracle]$ more /etc/redhat-release Red Hat Enterprise Linux AS release...
  • woshizyq
  • woshizyq
  • 2013年10月17日 21:41
  • 940

Memory Notification: Library Cache Object Loaded Into Sga

Applies to:Oracle Server Enterprise EditionThis problem can occur on any platform..SymptomsThe follo...
  • newzqs
  • newzqs
  • 2009年06月10日 12:13
  • 522

Memory Notification: Library Cache Object loaded into SGA / ORA-600 [KGL-heap-size-exceeded]

这些是不会导致进程失败的警告消息。 它们是以10gR2数据库版本开始引入的事件消息传递机制和内存管理器的结果。 由于共享池中的大对象可能会导致问题,因此已实施此警告阈值。 分配比此警告阈值更多的空间的...
  • qq_21127313
  • qq_21127313
  • 2017年05月19日 10:44
  • 132

[BUG]Memory Notification: Library Cache Object loaded into SGA

今天再厦门的测试库ALERT日志中发现了好多如下错误信息: 运行环境:REDHAT 4.0 + ORACLE 10.2.0.1 错误信息: Mon Aug 15 01:06:00 2011 E...
  • java3344520
  • java3344520
  • 2011年08月15日 17:58
  • 758

Memory Notification: Library Cache Object loaded into SGA 错误

1.问题现象 数据库日常巡检过程中,在alert日志中发现如下警告信息 ……省略…… Memory Notification: Library Cache Object loaded into ...
  • RuleV5
  • RuleV5
  • 2011年12月05日 13:35
  • 606

【问题处理】Memory Notification: Library Cache Object loaded into SGA

1.问题现象 数据库日常巡检过程中,在alert日志中发现如下警告信息 ……省略…… Thu Apr 15 22:06:31 2010 Memory Notification: Libra...
  • quanshuiwuxiang
  • quanshuiwuxiang
  • 2012年08月30日 11:47
  • 257

oracle Library Cache Object loaded into SGA

今天在测试数据库
  • suyishuai
  • suyishuai
  • 2014年05月06日 11:44
  • 534

Memory Notification: Library Cache Object loaded into SGA / ORA-600 [KGL-heap-size-exceeded] (文档 ID

APPLIES TO: Oracle Database - Enterprise Edition - Version 10.2.0.1 and later Information in this ...
  • qq_21127313
  • qq_21127313
  • 2017年05月05日 09:34
  • 612
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:Memory Notification: Library Cache Object loaded into SGA
举报原因:
原因补充:

(最多只允许输入30个字)