Oracle10g的library内存分配告警

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.

在Oracle10g的alert文件中，偶尔可以发现如下记录

Fri Feb 02 22:00:09 2007
Memory Notification: Library Cache Object loaded into SGA
Heap size 2448K exceeds notification threshold (2048K)
KGL object name :SELECT * FROM "ADMODB"."DW_SECTOR_FLAT"

Oracle10g中，在load较大的对象进library cache中时，会记录以上警告。在版本10.2.0.1中，这个定义大对象的阈值是2M，这是有隐含参数_kgl_large_heap_warning_threshold 指定的，如果系统中需要load很多大对象，又不想在alert中看到太多这类的警告信息，可以修改该参数：

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

参数值以字节为单位，设置完后需要重启实例才能生效。

从10.2.0.2起，这个参数的默认值被修改为50M。

这个错误跟经典的Ora-04031没有什么太大的关系。