[求助]关于10.2.0.1的Library Cache Object loaded into SGA警告

bewdx3

10.2.0.1的库,_kgl_large_heap_warning_threshold已经开到了100m
可是今天开始还是一直出现以下警告
Tue Jan 19 16:51:17 2010
Memory Notification: Library Cache Object loaded into SGA
Heap size 111824K exceeds notification threshold (102400K)

是否和sga的大小设置有关?
Total System Global Area 1.3640E+10 bytes
Fixed Size                  2017120 bytes
Variable Size            1895827616 bytes
Database Buffers         1.1727E+10 bytes
Redo Buffers               14757888 bytes

bewdx3

自己顶一下

棉花糖ONE

感觉是有问题,load 一个object 100多w,是有点恐怖了,可以看看v$db_object_cache是什么对象占用这么大内存

设置那个参数只是增加了warning的阀值,并不是解决了问题

[ 本帖最后由 棉花糖ONE 于 2010-1-20 10:44 编辑 ]

bewdx3

告警中的提示完整的是这样的:


Tue Jan 19 16:51:17 2010
Memory Notification: Library Cache Object loaded into SGA
Heap size 111824K exceeds notification threshold (102400K)
KGL object name  :SYS.ALERT_QUE

但是v$db_object_cache里除了大量的游标外没有单个SHARABLE_MEM占用特别大的,还有就是游标内容有大量重复的,这个是游标共享上的问题吗?

[ 本帖最后由 bewdx3 于 2010-1-20 16:07 编辑 ]

bewdx3

再顶一下

tom_fans

超过了就超过了，这只是一个警告而已，当你载入的对象超过了多少，ORACLE给你一个提示而已。

titan007

Subject:  Memory Notification: Library Cache Object Loaded Into Sga
  Doc ID:  Note:330239.1 Type:  PROBLEM
  Last Revision Date:  18-JUL-2006 Status:  PUBLISHED

In this Document
  Symptoms
  Changes
  Cause
  Solution
  References

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



Applies to: Oracle Server - Enterprise Edition - Version:
This problem can occur on any platform.
. SymptomsThe following messages are reported in alert.log after 10g Release 2 is installed.

        Memory Notification: Library Cache Object loaded into SGA
        Heap size 2294K exceeds notification threshold (2048K)

ChangesInstalled / Upgraded to 10g Release 2

CauseThese 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.  



SolutionIn 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.