oracle 10g警告日志太大,oracle10g日志报警问题解决一例

在开发过程中，发现日志中有以下错误出现，经过网上搜索，得出原因，并记录如下：

Memory Notification: Library Cache Object loaded into SGA

Heap size 3879K exceeds notification threshold (2048K)

KGL object name :SELECT USER OWNER, 1 PRIORITY FROM USER_OBJECTS WHERE OBJECT_NAME = :OBJECT_NAME and OBJECT_TYPE = :OBJECT_TYPE UNION ALL SELECT s.TABLE_OWNER OWNER, 2 PRIORITY FROM USER_SYNONYMS s, ALL_OBJECTS o WHERE s.SYNONYM_NAME = :OBJECT_NAME and s.TABLE_NAME = o.OBJECT_NAME and s.TABLE_OWNER = o.OWNER and o.OBJECT_TYPE = :OBJECT_TYPE UNION ALL SELECT s.TABLE_OWNER OWNER, 3 PRIORITY FROM ALL_SYNONYMS s, ALL_OBJECTS o WHERE s.SYNONYM_NAME = :OBJECT_NAME and s.OWNER = 'PUBLIC' and s.TABLE_NAME = o.OBJECT_NAME and s.TAB

bluemoon0083 2006-11-28 20:10

解决办法：

The messages are reported in alert.log after 10g Release 2 is installed.

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-403X is about to happen.

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

In 10.2.0.2,  the threshold is increased to 50MB after regression tests, so this should be a reasonable and recommended value.