今天上午检查某环境时发现alert日志有报错
Memory Notification: Library Cache Object loaded into SGA
Heap size 56508K exceeds notification threshold (51200K)
Details in trace file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_dw00_14402.trc
2023-05-14T21:03:55.870439+08:00
KGL object name :SELECT /*+all_rows*/ SYS_XMLGEN(VALUE(KU$), XMLFORMAT.createFormat2('TABLE_T', '7')), KU$.OBJ_NUM ,KU$.ANC_OBJ.NAME ,KU$.ANC_OBJ.OWNER_NAME ,KU$.ANC_OBJ.TYPE_NAME ,KU$.BASE_OBJ.NAME ,KU$.BASE_OBJ.OWNER_NAME ,KU$.BASE_OBJ.TYPE_NAME ,KU$.SPARE1 ,KU$.XMLSCHEMACOLS ,KU$.SCHEMA_OBJ.NAME ,KU$.SCHEMA_OBJ.NAME ,'TABLE' ,(select unique tag from sys.ku$_option_objnum_view opt where (opt.schema_obj.owner_name=KU$.SCHEMA_OBJ.OWNER_NAME and opt.schema_obj.name = KU$.SCHEMA_OBJ.NAME and opt.tgt_type=2) or(opt.schema_obj.
2023-05-14T21:04:51.014825+08:00
Memory Notification: Library Cache Object loaded into SGA
Heap size 55403K exceeds notification threshold (51200K)
Details in trace file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_dw00_14402.trc
Memory Notification: Library Cache Object loaded into SGA
Heap size 56508K exceeds notification threshold (51200K)
Details in trace file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_dw00_14402.trc
2023-05-15T08:03:55.870439+08:00
KGL object name :SELECT /*+all_rows*/ SYS_XMLGEN(VALUE(KU$), XMLFORMAT.createFormat2('TABLE_T', '7')), KU$.OBJ_NUM ,KU$.ANC_OBJ.NAME ,KU$.ANC_OBJ.OWNER_NAME ,KU$.ANC_OBJ.TYPE_NAME ,KU$.BASE_OBJ.NAME ,KU$.BASE_OBJ.OWNER_NAME ,KU$.BASE_OBJ.TYPE_NAME ,KU$.SPARE1 ,KU$.XMLSCHEMACOLS ,KU$.SCHEMA_OBJ.NAME ,KU$.SCHEMA_OBJ.NAME ,'TABLE' ,(select unique tag from sys.ku$_option_objnum_view opt where (opt.schema_obj.owner_name=KU$.SCHEMA_OBJ.OWNER_NAME and opt.schema_obj.name = KU$.SCHEMA_OBJ.NAME and opt.tgt_type=2) or(opt.schema_obj.
2023-05-15T08:04:51.014825+08:00
Memory Notification: Library Cache Object loaded into SGA
Heap size 55403K exceeds notification threshold (51200K)
Details in trace file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_dw00_14802.trc
处理办法
1、首先查看下现有的_kgl_large_heap_warning_threshold值大小
set pagesize 199 linesize 199;
col KSPPINM for a36;
col KSPPSTVL for a36;
select ksppinm, ksppstvl
from x$ksppcv cv, x$ksppi pi
where cv.indx = pi.indx
and pi.ksppinm like '_kgl_large_heap_warning_threshold%';
发现是默认值,确实没修改过,直接修改了
SQL> alter system set "_kgl_large_heap_warning_threshold"=82809856 scope=both ;
过几天再观察下,应该可以解决的,
ORA-600 [KGL-heap-size-exceeded] Errors in the Alert Log (Doc ID 2628072.1)
Memory Notification: Library Cache Object loaded into SGA / ORA-600 [KGL-heap-size-exceeded] (Doc ID 330239.1)