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] [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)

---

修改时间: 2014-2-7 类型: REFERENCE

In this Document Purpose Scope Details References APPLIES TO: Oracle Database - Enterprise Edition - Version 7.0.16.0 and later Oracle Database - Personal Edition Oracle Database - Standard Edition Information in this document applies to any platform. PURPOSE Note: For additional ORA-7445 related information please read Note:211909.1.   This article provides information on one or more bugs that are    known to result in the given ORA-7445 error. SCOPE ERROR:   ORA-7445 failing function "kkqstcrf" DETAILS SUGGESTIONS:   If the Known Issues section below does not help in terms of identifying   a solution, please submit the trace files and alert.log to Oracle    Support Services for further analysis.   Known Issues: Known Bugs 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. Other bugs may affect this version but have not been confirmed as being relevant yet.   There are 4 bugs listed. NB Bug Fixed Description 10184115 11.2.0.2.BP15, 11.2.0.3, 12.1.0.1 Wrong Results, Assorted Internal Errors and Dumps with ANSI join of tables with large number of columns 9050716 11.2.0.1.BP12, 11.2.0.2, 12.1.0.1 Dumps on kkqstcrf with ANSI joins and Join Elimination 8537544 11.2.0.2, 12.1.0.1 Excess memory use / ORA-600 / dumps parsing SQL with many nested outer joins / ANSI joins 8288908 11.2.0.2, 12.1.0.1 ORA-932 or dumps from query with many aggregations and query blocks ·         '*' 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].   REFERENCES NOTE:9050716.8 - Bug 9050716 - Dumps on kkqstcrf with ANSI joins and Join Elimination NOTE:10184115.8 - Bug 10184115 - Wrong Results, Assorted Internal Errors and Dumps with ANSI join of tables with large number of columns NOTE:211909.1 - Customer Introduction to ORA-7445 Errors NOTE:7915798.8 - Bug 7915798 - ORA-7445 [kkqstcrf] if fix for bug 6369463 present NOTE:8288908.8 - Bug 8288908 - ORA-932 or dumps from query with many aggregations and query blocks NOTE:8537544.8 - Bug 8537544 - Excess memory use / ORA-600 / dumps parsing SQL with many nested outer joins / ANSI joins

未找到您要查找的产品?在社区中提问...

Bug 9050716 - Dumps on kkqstcrf with ANSI joins and Join Elimination (文档 ID 9050716.8)

转到底部

---

修改时间: 2013-6-28 类型: PATCH

Bug 9050716  Dumps on kkqstcrf with ANSI joins and Join Elimination  This note gives a brief overview of bug 9050716.   The content was last updated on: 28-JUN-2013  Click here for details of each of the sections below. Affects: Product (Component) Oracle Server (Rdbms) Range of versions believed to be affected Versions >= 10.2.0.1 but BELOW 12.1 Versions confirmed as being affected 11.2.0.1 11.1.0.7 Platforms affected Generic (all / most platforms affected) Fixed: This issue is fixed in 12.1.0.1 (Base Release) 11.2.0.2 (Server Patch Set) 11.2.0.1 Bundle Patch 12 for Exadata Database 11.2.0.1 Patch 2 on Windows Platforms 11.1.0.7 Patch 26 on Windows Platforms Symptoms: Related To: Process May Dump (ORA-7445) / Abend / Abort Dump in or under kkqstUnswapOpns / kkqstcrf ANSI Joins Optimizer _OPTIMZIER_JOIN_ELIMINATION_ENABLED Description ANSI Query may dump on kkqstcrf (or possibly downstream code).     Workaround  _optimizer_join_elimination_enabled=false  no_eliminate_join hint   Please note: The above is a summary description only. Actual symptoms can vary. Matching to any symptoms here does not confirm that you are encountering this problem. For questions about this bug please consult Oracle Support. References Bug:9050716 (This link will only work for PUBLISHED bugs) Note:245840.1 Information on the sections in this article

 

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29785807/viewspace-2132120/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/29785807/viewspace-2132120/