Library Cache Object loaded into SGA Heap size 2226K exceeds notification threshold (2048K)

警告：
Memory Notification: Library Cache Object loaded into SGA
Heap size 2226K exceeds notification threshold (2048K)

原因：Oracle10g中，在load较大的对象进library cache中时，会记录以上警告。
      在版本10.2.0.1中（目前用的版本正是10.2.0.1），这个定义大对象的阈值是2M，这是由隐含参数
      _kgl_large_heap_warning_threshold 指定的。

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


--解决办法：
数据库版本为10.2.0.1.0
alter system set "_kgl_large_heap_warning_threshold" =10485760 scope=spfile;--10M
然后重启数据库
SQL> show parameter kgl;
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
_kgl_large_heap_warning_threshold    integer     10485760

延伸：
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的最大值则写入警告日志