本帖最后由 wzz123snow 于 2014-1-23 09:14 编辑
数据库日志当中有这个错误：
Memory Notification: Library Cache Object loaded into SGA
Heap size 2943K exceeds notification threshold (2048K)
KGL object name :select
service_name, uio_waits, uio_time, con_waits, con_time,
adm_waits, adm_time, net_waits, net_time
from
(select
stat1.service_name service_name, db_time, uio_waits, uio_time,
con_waits, con_time, adm_waits, adm_time, net_waits, net_time
from
(select s1.service_name,
sum(decode(s1.stat_name, 'DB time', s1.diff, 0)) db_time
from
(select e.service_name service_name,
e.stat_namestat_name,
查询了下，数据库版本是10.2.0.1，_kgl_large_heap_warning_threshold这个默认的隐含参数大小是2M，METLINK上建议改成8M或者升级版本。
Oracle XE on ubuntu (debian) - first impressionsDoes the oracle database run on Xenified Linux? ?10.2.0.1 Annoying message “Memory Notification: Library Cache Object loaded into SGA”
In the 10.2.0.1 version of the oracle database a new heap checking mechanism, together with a new messaging system is introduced. This new mechanism reports memory allocations above a threshold in the alert.log, together with a tracefile in the udump directory.
In certain situations it can be handy to know if large allocations are being done in the sga heap (shared pool), but only to troubleshoot memory allocation problems (which eventually will appear as the infamous ORA-4031). For normal day to day work, I doubt the effectivity of these messages.
The default limit is set at 2048K. It appears normal usage can result in messages from the memory manager. During my last installation (with all options set default, all options installed), this resulted in 125 messages from the memory manager.
Oracle gives a solution on metalink in note 330239.1:
Set _kgl_large_heap_warning_threshold to a “reasonable high” value. This parameter is set in bytes, and oracle recommends the value 8388608 (8192K).
C:\Documents and Settings\dba>sqlplus sys/ as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Wed Jan 22 15:21:56 2014
Copyright (c) 1982, 2005, Oracle.All rights reserved.
Enter password:
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
SQL> select a.ksppinm name,b.ksppstvl value,a.ksppdesc description
2from x$ksppi a,x$ksppcv b
3 where a.indx = b.indx
4 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> select 2097152/1024/1024 from dual;
2097152/1024/1024
-----------------
2
SQL> alter system set "_kgl_large_heap_warning_threshold"=8388608 scope=spfile;
System altered.
SQL> select 8388608/1024/1024 from dual;
8388608/1024/1024
-----------------
8
之后重启数据库
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 1.7180E+10 bytes
Fixed Size
2034464 bytes
Variable Size
520094944 bytes
Database Buffers 1.6643E+10 bytes
Redo Buffers
14741504 bytes
Database mounted.
Database opened.
SQL> select a.ksppinm name,b.ksppstvl value,a.ksppdesc description
2from x$ksppi a,x$ksppcv b
3where a.indx = b.indx
4and a.ksppinm = '_kgl_large_heap_warning_threshold';
NAME
---------------------------------------------------------------------------
VALUE
---------------------------------------------------------------------------
DESCRIPTION
---------------------------------------------------------------------------
_kgl_large_heap_warning_threshold
8388608
maximum heap size before KGL writes warnings to the alert log
更多精彩内容，请关注我的博客。
wzz123snow的博客 |
