昨天給某客戶做巡檢，檢查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的執行計劃，因此，還是應該找時間將相關補本打上來解決這個問題。

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

相關文章