这些是不会导致进程失败的警告消息。 它们是以10gR2数据库版本开始引入的事件消息传递机制和内存管理器的结果。 由于共享池中的大对象可能会导致问题,因此已实施此警告阈值。 分配比此警告阈值更多的空间的项目/ SQL将向警报日志输出警告。 此警告仅用于通知给定的堆对象超出了定义的阈值大小,并生成跟踪文件,以便DBA可以从共享内存的角度检查对象的潜在昂贵。 这些是纯粹的警告消息,对数据库功能没有任何影响,尽管它们旨在表明客户应用程序中可能的调优机会。 消息并不意味着ORA-4031即将发生,除非共享池的大小非常小。
APPLIES TO:
Oracle Database - Enterprise Edition - Version 10.2.0.1 and laterInformation in this document applies to any platform.
***Checked for relevance 17-Aug-2014***
.
SYMPTOMS
In alert log there are reported messages like the following:
Memory Notification: Library Cache Object loaded into SGA
Heap size <heap size K> exceeds notification threshold (51200K)
CAUSE
These are warning messages that are not causing process failure. They appear as a result of event messaging mechanism and memory manager introduced starting with 10gR2 database release. As large objects in the shared pool can potentially cause problems this warning threshold was implemented. Items/SQLs which allocate more space than this warning threshold, outputs a warning to the alert log. This warning is only to inform that a given heap object exceeds the defined threshold size and a trace file is generated so that a DBA can check potentially expensive - from shared memory point of view - objects. These are purely warning messages and have no impact on the database functionality, although they are designed to indicate possible tuning opportunities in customers' applications. The messages do not imply that an ORA-4031 is about to happen immediately unless the size of shared pool is very small.
SOLUTION
A hidden parameter - _kgl_large_heap_warning_threshold - that sets the KGL heap size warning threshold was introduced starting with 10gR2. Warnings are written if heap size in shared pool exceeds this threshold:
Besides reducing the heap size from the application code (recommended) one can set _kgl_large_heap_warning_threshold to a reasonable high value or zero to prevent these warning messages. The value needs to be set in bytes. For example:
If using a SPFILE:
=============
(logged in as "/ as sysdba")
SQL> alter system set "_kgl_large_heap_warning_threshold"=83886080 scope=spfile ;
SQL> shutdown immediate
SQL> startup
If using a PFILE:
============
Edit the PFILE and add:
The default threshold in 10.2.0.1 is only 2M. Starting with 10.2.0.2 the threshold was increased to 50MB after regression tests, so this should be a reasonable and recommended value in most cases.
In 12.1.0.2 database release (that includes the fix Bug 15898589 - enhancement to restrict the size of SGA base library cache heaps) an enhancement to restrict the size of SGA base library cache heaps was introduced in order to avoid running out of space inside shared pool and hence ORA-4031 errors. With this fix, a new hidden parameter - _kgl_large_heap_assert_threshold - was also introduced.
Its value represents the maximum heap size before raising the ORA-600 internal error like:
To check current value of the parameters, one can run the following query:
nam.ksppinm NAME,
nam.ksppdesc DESCRIPTION,
val.KSPPSTVL
from
x$ksppi nam,
x$ksppsv val
where nam.indx = val.indx and nam.ksppinm like '%kgl_large_heap_%_threshold%';
For example in 12.1.0.2 the default values for the two parameters are:
SQL> select
2 nam.ksppinm NAME,
3 nam.ksppdesc DESCRIPTION,
4 val.KSPPSTVL
5 from
6 x$ksppi nam,
7 x$ksppsv val
8 where nam.indx = val.indx and nam.ksppinm like '%kgl_large_heap_%_threshold%';
NAME
--------------------------------------------------------------------------------
DESCRIPTION
--------------------------------------------------------------------------------
KSPPSTVL
--------------------------------------------------------------------------------
_kgl_large_heap_warning_threshold
maximum heap size before KGL writes warnings to the alert log
52428800
_kgl_large_heap_assert_threshold
maximum heap size before KGL raises an internal error
524288000
Please be aware that by setting _kgl_large_heap_warning_threshold to 0 in 12.1.0.2 it is caused the problem described in:
Bug 22330282 - "Heap size 0K exceeds notification threshold" alert messages when "_kgl_large_heap_warning_threshold" is set to 0 (Document: 22330282.8)
To fix this problem either:
==> Apply Patch 22330282 or open a backport request with Oracle Support if the patch is not available for your database release and/or platform.
or
==> Either of following options may workaround the issue:
a. Set _kgl_large_heap_warning_threshold to a very large value.
b. Set both _kgl_large_heap_warning_threshold and _kgl_large_heap_assert_threshold to zero.