告警日志中报错ORA-07445 kkqstcrf

问题描述:数据库例行巡检时发现告警日志中报错ORA-07445 kkqstcrf,如下所示:
数据库:oracle 11.2.0.1
告警日志:
Sun Dec 25 22:08:22 2022
Begin automatic SQL Tuning Advisor run for special tuning task "SYS_AUTO_SQL_TUNING_TASK"
Exception [type: SIGSEGV, Address not mapped to object] [ADDR:0x71] [PC:0x1CBAD19, kkqstcrf()+1355] [flags: 0x0, count: 1]
Errors in file /u01/app/oracle/diag/rdbms/emr/emr/trace/emr_j002_5566.trc (incident=52274):
ORA-07445: exception encountered: core dump [kkqstcrf()+1355] [SIGSEGV] [ADDR:0x71] [PC:0x1CBAD19] [Address not mapped to object] []
Incident details in: /u01/app/oracle/diag/rdbms/emr/emr/incident/incdir_52274/emr_j002_5566_i52274.trc
异常分析:
ora-07445告警频率:周末从6点开始每隔4小时报警一次,直到22点;周一到周五在每天的22点左右报警一次.根据MOS [ID 977299.1]文档,此异常与BUG 9050716匹配.
从incident文件可以找到触发该告警的sql语句.
解决方案:
--以下列出两个解决方案:
a、禁用隐藏参数”_optimizer_join_elimination_enabled”,语句如下:
alter system set “_optimizer_join_elimination_enabled”=false scope=both;
如下语句查询该隐含参数值:
select a.ksppinm name, b.ksppstvl value, a.ksppdesc description
from x$ksppi a,x$ksppcv b
where a.indx=b.indx
and a.ksppinm='_optimizer_join_elimination_enabled';
b、将数据库从11.2.0.1升级到11.2.0.4
参考文档
http://blog.itpub.net/29785807/viewspace-2287062/