ORA-07445: 出现异常错误: 核心转储 [kkqstcrf()+1355]
数据库版本:Oracle 11.2.0.1.0
数据库服务器操作系统:Red Hat Enterprise Linux Server release 6.4
问题现象:业务人员在前台执行某些查询操作时,报错:"无法从套接字读取更多的数据";
后台数据库警告日志报错如下:
ORA-07445: 出现异常错误: 核心转储 [kkqstcrf()+1355] [SIGSEGV] [ADDR:0x71] [PC:0x1CCD56D] [Address not mapped to object] []
Incident details in: /opt/oracle/app/diag/rdbms/orcl/orcl/incident/incdir_137596/orcl_ora_24196_i137596.trc
查看alert警告日志,查找对应的trace文件,找到触发此bug的sql语句如下:
----- Current SQL Statement for this session (sql_id=g2mq0cq2pqnu5) -----
select bd_psndoc.name psnname,
bd_psndoc.code psncode,
hi_psnjob.clerkcode,
org_dept_v.name deptname,
org_orgs_v.name orgname,
bd_psncl.name plsname,
financeorg.name financeorg,
financedept.name financedept,
liabilityorg.name liabilityorg,
liabilitydept.name liabilitydept,
om_job.jobname,
om_post.postname,
wa_data.*,
datapower.operateflag
from wa_data
inner join bd_psndoc
on wa_data.pk_psndoc = bd_psndoc.pk_psndoc
inner join hi_psnjob
on wa_data.pk_psnjob = hi_psnjob.pk_psnjob
left outer join org_orgs_v
on org_orgs_v.pk_vid = wa_data.workorgvid
left outer join org_dept_v
on org_dept_v.pk_vid = wa_data.workdeptvid
left outer join om_job
on hi_psnjob.pk_job = om_job.pk_job
left outer join om_post
on hi_psnjob.pk_post = om_post.pk_post
left outer join bd_psncl
on hi_psnjob.pk_psncl = bd_psncl.pk_psncl
left outer join org_orgs financeorg
on wa_data.pk_financeorg = financeorg.pk_org
left outer join org_dept financedept
on wa_data.pk_financedept = financedept.pk_dept
left outer join org_orgs liabilityorg
on wa_data.pk_liabilityorg = liabilityorg.pk_org
left outer join org_dept liabilitydept
on wa_data.pk_liabilitydept = liabilitydept.pk_dept
left outer join (select 'Y' operateflag, pk_wa_data
from wa_data
where (pk_org in
(select distinct pk_org
from sm_subject_org
where pk_group = 'AAAAAAAAAAAAAAAAAAAA'
and subjectid in
(select pk_role
from sm_user_role
where cuserid = 'BBBBBBBBBBBBBBBBBBBB'
and enabledate <= '2007-02-09 10:36:55'
and (nvl(to_char(disabledate), '~') = '~' or
disabledate >
'2007-02-09 10:36:55'))) or
pk_org in
('CCCCCCCCCCCCCCCCCCCC', 'AAAAAAAAAAAAAAAAAAAA'))) datapower
on wa_data.pk_wa_data = datapower.pk_wa_data
where wa_data.pk_wa_data in (select in_pk from hr_temptable_0);
---SQL具体值已做处理;
解决方案:
trace里当前SQL引用多个left outer join,在11.2.0.1.0版本,外关联存在多个BUG;
查看MOS和BUG 9050716比较匹配,禁用_optimizer_join_elimination_enabled参数问题解决;
SQL> alter system set "_optimizer_join_elimination_enabled"=false scope=both;
---查看_optimizer_join_elimination_enabled参数值
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';
ORA-7445 [kkqstcrf] (文档 ID 977299.1) |
|
|
In this Document
APPLIES TO: Oracle Database - Enterprise Edition - Version 7.0.16.0 and later Note: For additional ORA-7445 related information please read Note:211909.1. ERROR: SUGGESTIONS: You can restrict the list below to issues likely to affect one of the following versions by clicking the relevant button: The list below is restricted to show only bugs believed to affect version 11.2.0.1.
There are 4 bugs listed.
· '*' indicates that an alert exists for that issue. · '+' indicates a particularly notable issue / bug. · 'P' indicates a port specific bug. · "OERI:xxxx" may be used as shorthand for ORA-600 [xxxx].
NOTE:9050716.8 - Bug 9050716 - Dumps on kkqstcrf with ANSI joins and Join Elimination |
未找到您要查找的产品?在社区中提问...
Bug 9050716 - Dumps on kkqstcrf with ANSI joins and Join Elimination (文档 ID 9050716.8) |
|
|
Bug 9050716 Dumps on kkqstcrf with ANSI joins and Join Elimination This note gives a brief overview of bug 9050716. Affects:
Fixed:
Description ANSI Query may dump on kkqstcrf (or possibly downstream code).
Workaround _optimizer_join_elimination_enabled=false no_eliminate_join hint
References Bug:9050716 (This link will only work for PUBLISHED bugs) |
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/29785807/viewspace-2132120/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/29785807/viewspace-2132120/