ORA-00600: \[KGL-heap-size-exceeded]告警分析处理过程

告警信息如下：
Memory Notification: Library Cache Object loaded into SGA
Heap size 512006K exceeds notification threshold (51200K)
Details in trace file /oracle/diag/rdbms/xxxx4/xxxx43/trace/xxxx43_ora_300669.trc
2021-06-27T05:43:00.601195+08:00
KGL object name :select vservicesr0_.SERVICEID as SERVICEID7_, vservicesr0_.SERVICENAME as SERVICEN2_7_, vservicesr0_.SERVICEFULLNAME as SERVICEF3_7_, vservicesr0_.REALURL as REALURL7_, vservicesr0_.SOAPURL as SOAPURL7_, vservicesr0_.INREALURL as INREALURL7_, vservicesr0_.INSOAPURL as INSOAPURL7_, vservicesr0_.SERVICETYPE as SERVICET8_7_, vservicesr0_.FULLPERMISSION as FULLPERM9_7_, vservicesr0_.FUNCTIONTYPE as FUNCTIO10_7_, vservicesr0_.CAPABILITIES as CAPABIL11_7_, vservicesr0_.CLUSTERCODE as CLUSTER12_7_, vservicesr0_.CL
Errors in file /oracle/diag/rdbms/xxxx4/xxxx43/trace/xxxx43_ora_300669.trc (incident=324889):
ORA-00600: ÄÚ²¿´íÎó´úÂë, ²ÎÊý: [KGL-heap-size-exceeded], [0x0FA0E4EC0], [0], [524294800], [], [], [], [], [], [], [], []
Incident details in: /oracle/diag/rdbms/xxxx4/xxxx43/incident/incdir_324889/xxxx43_ora_300669_i324889.tr


首先分析这个告警是“Memory Notification”也就是内存通知消息，不是warning，所以这个问题应该不会影响数据库或者进程正常
运行。这个功能是10gR2引入的内存管理和事件消息通知机制。共享池中存放的对象超过了引擎设置的门限值，则告警提示。
由于目前没有报４０３１错误，可以不考虑共享池大小问题。

我们查询这个两个参数的大小。

SQL> select nam.ksppinm name,nam.ksppdesc description ,val.ksppstvl from x$ksppi nam,x$ksppsv val
where nam.indx = val.indx and nam.ksppinm like '%kgl_large_heap_%_threshold%';

NAME
--------------------------------------------------------------------------------
DESCRIPTION
--------------------------------------------------------------------------------
KSPPSTVL
--------------------------------------------------------------------------------
_kgl_large_heap_warning_threshold
maximum heap size before KGL writes warnings to the alert log
52428800

_kgl_large_heap_assert_threshold
maximum heap size before KGL raises an internal error
524288000

NAME
--------------------------------------------------------------------------------
DESCRIPTION
--------------------------------------------------------------------------------
KSPPSTVL
--------------------------------------------------------------------------------
这个两个参数的解释比较清楚，_kgl_large_heap_warning_threshold是告警阀值，_kgl_large_heap_assert_threshold是heap的大小
告警阀值。

从告警看，确实是这个问题：
Heap size 512006K exceeds notification threshold (51200K)

修改两个隐藏参数解决
SQL> alter system set "_kgl_large_heap_warning_threshold"=524288000 scope=both;　　<<<<<<告警阀值

System altered.

SQL> alter system set "_kgl_large_heap_assert_threshold"=524288000 scope=both;　　<<<<<<大小阀值

System altered.

修改效果
SQL> select nam.ksppinm name,nam.ksppdesc description ,val.ksppstvl from x$ksppi nam,x$ksppsv val
where nam.indx = val.indx and nam.ksppinm like '%kgl_large_heap_%_threshold%';

NAME
--------------------------------------------------------------------------------
DESCRIPTION
--------------------------------------------------------------------------------
KSPPSTVL
--------------------------------------------------------------------------------
_kgl_large_heap_warning_threshold
maximum heap size before KGL writes warnings to the alert log
524288000

_kgl_large_heap_assert_threshold
maximum heap size before KGL raises an internal error
524288000

NAME
--------------------------------------------------------------------------------
DESCRIPTION
--------------------------------------------------------------------------------
KSPPSTVL
--------------------------------------------------------------------------------