Memory Notification: Library Cache Object loaded into SGA

最新推荐文章于 2019-08-19 15:58:27 发布

ORA_无花果

最新推荐文章于 2019-08-19 15:58:27 发布

阅读量303

点赞数

分类专栏：维护日常故障处理文章标签： Memory Notification

本文链接：https://blog.csdn.net/chjwhq/article/details/17075841

版权

维护同时被 2 个专栏收录

4 篇文章 0 订阅

订阅专栏

日常故障处理

1 篇文章 0 订阅

订阅专栏

环境：

linux AS 5.5 64bit

oracle 10.2.0.1

1.问题现象

检查alert日志中发现如下警告信息

Current log# 2 seq# 7982 mem# 0: /hrdata/oradata/HDEASHR/redo02.log
Mon Dec 2 10:13:30 2013
Memory Notification: Library Cache Object loaded into SGA
Heap size 6605K exceeds notification threshold (2048K)
Details in trace file /hrdata/admin/HDEASHR/udump/hdeashr_ora_23215.trc
KGL object name :CREATE TABLE VTTPN09TZ1CMC0VIYW6G939RIDL AS SELECT * FROM (SELECT DISTINCT "PERSON".FID "FPERSONID", "PERSON".FName_l2 "FPER001", "PERSON".FNumber "FPER002", "PERSON".FIndex "INDEXOF", "ATTACHDEPT".FDisplayName_l2 "FPER003", "ATTACHDEPT".FNumber "FPER004", "ATTACHDEPT".FName_l2 "FPER005", "ATTACHDEPT".FLongNumber "ATTACHDEPT.LONGNUMBER", "ATTACHDEPT".FSortCode "ATTACHDEPT.SORTCODE", "POSITION".FName_l2 "FPER006", "POSITION".FNumber "POSITIONNUMBER", "POSITION".FSortCode "POSITION.SORTCODE", "POSITION".FInd
Mon Dec 2 10:13:30 2013
Memory Notification: Library Cache Object loaded into SGA
................

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
SQL> 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的默认大小
SQL> 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

SQL> select 2097152/1024/1024 MB from dual;

        MB
----------
         2

3.问题处理方法
既然知道了问题原因，处理起来就很简单了。如果不希望在alert文件中看到这些报错，可以适当调大隐含参数“_kgl_large_heap_warning_threshold”的值，或将其设置为“0”。
1）将_kgl_large_heap_warning_threshold参数大小调整为50M
SQL> alter system set "_kgl_large_heap_warning_threshold"=52428800 scope=spfile;

System altered.
2）重启数据库
OK，该问题到此已得到有效处理。