oracle ora.crf,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

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.

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

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

11.2.0.1.BP12, 11.2.0.2, 12.1.0.1

Dumps on kkqstcrf with ANSI joins and Join

Elimination

11.2.0.2, 12.1.0.1

Excess memory use / ORA-600 / dumps parsing SQL

with many nested outer joins / ANSI joins

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].

<1span style="font-size:7.0pt;font-family:" color:white;"="">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

Platforms affected

Generic (all / most platforms affected)

Fixed:

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

(This link will only work for

PUBLISHED bugs)

Note:245840.1

Information on the sections in this article

欢迎关注我的微信公众号"IT小Chen"，共同学习，共同成长！！！