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的最大值則寫入警告日誌