Memory Notification: Library Cache Object loaded into SGA

http://www.cnblogs.com/huangjingzhou/articles/2224587.html

 

Memory Notification: Library Cache Object loaded into SGA问题

问题:alert_SID.log出现

Memory Notification: Library Cache Object loaded into SGA

Heap size 2289K exceeds notification threshold (2048K)

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.

下面是oracle上的metalink信息：

Memory Notification: Library Cache Object Loaded Into Sga

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

NOTE: The default threshold in 10.2.0.1 is 2M. So these messages could show up frequently in some application environments.

In 10.2.0.2, the threshold was increased to 50MB after regression tests, so this should be a reasonable and recommended value. If you continue to see the these warning messages in the alert log after applying 10.2.0.2 or higher, please contact Oracle Support Services for further investigation of any possible shared pool problems.

 

--X$KSPPI, X$KSPPCV 必须以sys用户登录查看
SQL> select ki.KSPPINM NAME, kv.KSPPSTVL VALUE, kv.KSPPSTDVL DISPLAY_VALUE
      from X$KSPPI ki, X$KSPPCV kv
     where ki.indx = kv.indx
       and ki.KSPPINM like '%&v_parameter%';

NAME                                                                             VALUE                                                                            DISPLAY_VALUE
_kgl_multi_instance_lock                                                    TRUE                                                                             TRUE
_kgl_multi_instance_pin                                                      TRUE                                                                             TRUE
_kgl_multi_instance_invalidation                                            TRUE                                                                             TRUE
_kgl_latch_count                                                                 0                                                                                     0
_kgl_heap_size                                                                   1024                                                                                1024
_kgl_fixed_extents                                                              TRUE                                                                                TRUE
_kgl_session_cached_objects                                                10                                                                                    10
_kgl_keep_cache_pct                                                            30                                                                                    30
_kgl_keep_cache_retain_pct                                                   20                                                                                   20
_kgl_bucket_count                                                                9                                                                                     9
_kglsim_maxmem_percent                                                      5                                                                                    5
_kgl_hash_collision                                                              FALSE                                                                                FALSE
_kgl_time_to_wait_for_locks                                                  15                                                                                    15
_kgl_large_heap_warning_threshold                                        2097152    


延伸：
1、oracle的隐含参数（以_开头的参数），无法直接通过 show parameter来查看，可以通过X$KSPPI和X$KSPPCV视图来查看（sys用户）。
2、_kgl_large_heap_warning_threshold值以字节为单位
3、从oracle10.2.0.2起，这个参数的默认值被修改为50M
select KSPPDESC from x$ksppi where ksppinm like '_kgl_large_heap_warning%';
maximum heap size before KGL writes warnings to the alert log --表明如果超出该kgl的最大值则写入警告日志