[重庆思庄每日技术分享]-多表关联执行报错 ORA-07445:[kkqstcrf()+1355]

晚上一SQL执行报错，具体报错：

Sun Apr 24 22:00:12 2022

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/jhemrdb/jhemr/trace/jhemr_j000_18569.trc (incident=38945):

查MOS上发现是11.2.0.1 的一个 bug, 具体如下：

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: 08-MAR-2022

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:

The fix for 9050716 is first included 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

Interim patches may be available for earlier versions - click here to check.

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