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 错误

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

Memory Notification: Library Cache Object loaded into SGA

Memory Notification: Library Cache Object loaded into SGA 1.问题现象 数据库日常巡检过程中,在alert日志中发现如下警告信息 ……省...

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

Memory Notification: Library Cache Object loaded into SGA

转自:http://space.itpub.net/519536/viewspace-659979 1.问题现象 数据库日常巡检过程中,在alert日志中发现如下警告信息 ……省...

Memory Architecture-SGA-Database Buffer Cache

Memory Architecture-SGA-Database Buffer Cache

真机调试报错:Library not loaded: @rpath/AFNetworking.framework/AFNetworking

在运行的时候,编译通过, 但程序在启动的时候,程序直接崩溃,显示如下: dyld: Library not loaded: @rpath/AFNetworking.framework/AFNetwo...
  • ridxmc
  • ridxmc
  • 2017年01月18日 00:05
  • 1498

QT 无法链接动态库 dyld library not loaded ... libhdf5.100.dylib

qt dyld library not loaded …/libhdf5.100.dylib分析无法链接动态库,考虑手动添加链接。...
  • huihut
  • huihut
  • 2017年06月13日 23:08
  • 446

IOS 10 Xcode8 dyld: Library not loaded: Reason: image not found

dyld: Library not loaded: /System/Library/Frameworks/UserNotifications.framework/UserNotifications ...
  • YLGWHYH
  • YLGWHYH
  • 2016年11月09日 17:54
  • 1757

Failed to connect to the specified server. Server library could not be loaded

这个错误是前几天用户提出了的,我今天又一次遇到了,特拿出来跟大家分享一下 问题背景: 使用ArcCatalog直连远程服务器,数据库为Oracle,提示“Error: Server libra...
  • lrh_079
  • lrh_079
  • 2012年02月17日 20:27
  • 4578

Selected SVN connector library is not available or cannot be loaded

1、错误描述The following data will be sent: ------ STATUS ------ pluginId org.eclipse.team.sv...
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:Memory Notification: Library Cache Object loaded into SGA
举报原因:
原因补充:

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