ORA-07445: exception encountered: core dump [qkaqkn()+5390] [SIGSEGV]

昨天给某客户做巡检，检查alert警告日志文件发现ORA-07445: exception encountered: core dump [qkaqkn()+5390] [SIGSEGV] [Address not mapped to object]错误，客户环境是两节点RAC，数据库版本是10.2.0.5.0，两节点的alert警告日志文件中都报这个错误，打开对应的trace文件，错误如下：
ksedmp: internal or fatal error
ORA-07445: exception encountered: core dump [qkaqkn()+5390] [SIGSEGV] [Address not mapped to object] [0x000000020] [] []
Current SQL statement for this session:
SELECT LL.WORK_DATE,
       LL.WORK_SHIFT,
       LL.PDLINE_NAME,
       LL.TARGET,
       LL.FAIL,
       LL.RATE,
       LL.DARATE,
       NVL(GZL.RUN_TIME,0) RUN_TIME,
       NVL(GZL.DOWN_TIME,0) DOWN_TIME,
       NVL(GZL.DOWN_RATE,0) DOWN_RATE,
       /*PLL.PICKUP_QTY,
       PLL.MISS_QTY,
       PLL.M_RATE,*/
       NVL(HX.HX_TIME,0) HX_TIME,
       NVL(HX.HX_COUNT,0) HX_COUNT,
       NVL(HX.HX_AVG_TIME,0) HX_AVG_TIME,
       NVL(PVT.PVT_TIME,0) PVT_TIME,
       NVL(PVT.PVT_COUNT,0) PVT_COUNT,
       NVL(PVT.PVT_AVG_TIME,0) PVT_AVG_TIME,
       NVL(DVT.DVT_TIME,0) DVT_TIME,
       NVL(DVT.DVT_COUNT,0) DVT_COUNT,
       NVL(DVT.DVT_AVG_TIME,0) DVT_AVG_TIME/*,
       RE.RE*/
  FROM (
           SELECT WORK_DATE,
                SHIFT WORK_SHIFT,
                LINE PDLINE_NAME,
                SUM(RLH.TARGET_QTY) AS TARGET,
                SUM(RLH.FAIL_QTY) AS FAIL,
                (CASE SUM(RLH.ACTUAL_QTY + RLH.FAIL_QTY)
                  WHEN (0) THEN
                   0
                  ELSE
                   ROUND(100 * SUM(RLH.ACTUAL_QTY) /
                         SUM(RLH.ACTUAL_QTY + RLH.FAIL_QTY),
                         2)
                END) AS RATE,
                SUM(RLH.ACTUAL_QTY + RLH.FAIL_QTY) AS OUTPUT_QTY,
                (CASE SUM(RLH.TARGET_QTY)
                  WHEN (0) THEN
                   0
                  ELSE
                   ROUND(100 * SUM(RLH.ACTUAL_QTY) / SUM(RLH.TARGET_QTY), 1)
                END) AS DARATE
          FROM jzhtable@jzhdb RLH

正是上面的语句引起的错误，查看mos发现了一篇文章：
Query Via DBLINK Fails With Ora-07445: Exception Encountered: Core Dump [Qkaqkn()+4744] [Sigsegv] (文档 ID 1130973.1)
APPLIES TO:
Oracle Database - Enterprise Edition - Version 11.2.0.1.0 and later
Information in this document applies to any platform.
CAUSE：
The problem seems to be related to unpublished Bug 9061785 where there is a crash in qka for a query block containing either union all or outer joined views, and some references to remote tables.
The bug incorrectly pushes join predicates into a view resulting in ORA-7445.
SOLUTION
Either:
Apply 11.2.0.2 or above where the problem is fixed
or:
Check Patch 9061785 for the availability of one-off fixes on your version and platform
or:
Workaround the problem by disabling Join Predicate Push Down (JPPD) by setting "_push_join_predicate" = false and/or "_push_join_union_view" = false.
This will stop the optimizer from attempting to push join predicates into a view.
REFERENCES
BUG:9597175 - ORA-7445 [QKAQKN] ON QUERY OVER DBLINK TO 10.2.0.4
根据mos文章描述此错误是由于远程通过dblink对表查询导致，见标红字体，这是一个未公开的bug，在10.2.0.4时就已经出现了，直到11.2.0.2才被修复。
解决方法是
1.将数据库版本升至11.2.0.2
2.打相关补丁
3.将 _push_join_predicate和 _push_join_union_view两个隐含参数，但是很可能改变sql的执行计划
这两个隐含参数翻译大概意思是推进谓词和视图连接，下面来看看这两个隐含参数的定义：
SQL> select ksppdesc from x$ksppi where ksppinm='_push_join_predicate';
KSPPDESC
--------------------------------------------------------------------------------
enable pushing join predicate inside a view

SQL> select ksppdesc from x$ksppi where ksppinm='_push_join_union_view';
KSPPDESC
--------------------------------------------------------------------------------
enable pushing join predicate inside a union all view
根据定义描述为启用推进谓词至view 连接或者union all view连接，如果将这两个隐含参数设置为false，很可能改变sql的执行计划，因此，还是应该找时间将相关补本打上来解决这个问题。

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

转载于:http://blog.itpub.net/10271187/viewspace-1313894/