ORA-07445: 出现异常错误: 核心转储 [kkqstcrf()+1355]

摘要

ORA-07445: 出现异常错误: 核心转储 [kkqstcrf()+1355] 数据库版本：Oracle 11.2.0.1.0数据库服务器操作系统：Red Hat Enterprise Linux Server release 6.4问题现象：业务人员在前台执行某些查询操作时，报错："无法从套接字读取更多的数据"；后台数据库警告日志报

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';