多表关联执行报错 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