今天检查生产库的日志文件，发现很多如下提示

Memory Notification: Library Cache Object loaded into SGA

Heap size 2765K exceeds notification threshold (2048K)

Details in trace file /oracle/product/10.2.0/db_1/admin/ssdb/udump/ssdb_ora_7951.trc

KGL object name :select e.tsname tsname,

sum(e.phyrds - nvl(b.phyrds,0)) reads,

sum(e.phyrds - nvl(b.phyrds,0)) / :ela rps,

decode(sum(e.phyrds - nvl(b.phyrds, 0)),

0, 0,

10 * (sum(e.readtim - nvl(b.readtim, 0)) /

sum(e.phyrds - nvl(b.phyrds, 0)))) atpr,

decode(sum(e.phyrds - nvl(b.phyrds,0)),

0, 0,

原来，在Oracle 10.2.0.1 版本数据库中隐含参数 _kgl_large_heap_warning_threshold 默认值是2M，该参数控制加载到内存中对象的大小，当加载的对象大于2M时，就会在alert警告文件中进行提示。2M的默认大小相对太小，因此在10.2.0.1版本中可能很容易遇到这个报错信息。该参数默认值在10.2.0.2版本中进行了调整，调整到了50M。

查看当前数据库的版本

SQL> select * from v$version;

BANNER

----------------------------------------------------------------

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bi

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

再看看该隐含参数的默认值

SQL> 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

SQL>

经确认，当前版本确实是10.2.0.1，而那个隐含参数的默认值确实也只有2M，如是修改该参数

SQL> alter system set "_kgl_large_heap_warning_threshold"=52428800 scope=spfile;

System altered.

SQL>

修改后，重启数据库即可。

ITPub博客

Memory Notification: Library Cache Object loaded into SGA

最新文章