oracle 11.2.0.1 +linux，报 DDE: Problem Key 'ORA 7445 [kkqstcrf()+1355]'

郑全

现象：
       SQL语句执行，直接报：
           ora-03113: 通信通道的文件结尾：
           进程id: 27532
           会话id: 922 序列号:1929
变动：
      从WINDOWS主库切换到LINUX备库
环境：
　　ｌｉｎｕｘ　７．１
　　ｏｒａｃｌｅ　：１１．２．０．１


分析：
        同样的SQL语句，在ｗｉｎｄｏｗｓ时就没有问题。
　　ｓｑｌ语句如下：
　　SELECT  (SELECT CASE
                  WHEN T.AGE IS NULL THEN
                   '0'
                  WHEN T.AGE LIKE '%月' THEN
                   '0'
                  WHEN T.AGE LIKE '%天' THEN
                   '0'
                  WHEN T.AGE LIKE '%小时' THEN
                   '0'
                  WHEN T.AGE LIKE '%分钟' THEN
                   '0'
                  WHEN T.AGE LIKE '%岁' THEN
                   CASE
                     WHEN T.AGE LIKE '%/%岁' THEN
                      CASE
                        WHEN T.AGE LIKE '% %' THEN
                         REGEXP_SUBSTR(T.AGE, '[0-9]+') -- [0-9]+/?[0-9]+'
                       ELSE
                        REGEXP_SUBSTR(T.AGE, '[0-9]+/?[0-9]+')
                     END
                    ELSE
                     REGEXP_SUBSTR(T.AGE, '[0-9]+')      
                  END
               END
          FROM PAT_VISIT T
         WHERE B.PATIENT_ID = T.PATIENT_ID
           AND B.VISIT_ID = T.VISIT_ID) AS NL --年龄

  FROM PAT_MASTER_INDEX A
  LEFT JOIN PAT_VISIT B
    ON A.PATIENT_ID = B.PATIENT_ID
  LEFT JOIN PAT_VISIT1 C
    ON B.PATIENT_ID = C.PATIENT_ID
   AND B.VISIT_ID = C.VISIT_ID
LEFT JOIN FIRST_PAGE_COSTS D
    ON B.PATIENT_ID = D.PATIENT_ID
   AND B.VISIT_ID = D.VISIT_ID
   AND B.HOSPITAL_NO = D.HOSPITAL_NO

　根据以上问题，初步判断，应该是BUG,
　最后，在MOS上找到：
　　This issue is due to a Bug in the Oracle Database:

Bug 9856699 ORA-7445 KKQSTCRF() GENERATING ON A SELECT STATEMENT HAVING LEFT OUTER JOIN

This bug is a duplicate of the unpublished Bug 9050716 ORA-7445 [KKQSTCRF()+1348] [SIGSEGV] DURING EXPLAIN PLAN

As per the Informatica Session Log file for SIL_WorkforceEventFact, the Source Qualifier SQL query is executed with the ANSI Joins "as-is", for example:

SELECT TAB.ASSIGNMENT_ID, TAB.EFFECTIVE_START_DATE, ...
FROM
(SELECT ...
FROM W_WRKFC_EVT_FS A LEFT OUTER JOIN W_WRKFC_EVENT_TYPE_D EVT
   ON EVT.INTEGRATION_ID = A.EVENT_TYPE_RSN_ID AND EVT.DATASOURCE_NUM_ID = A.DATASOURCE_NUM_ID
LEFT OUTER JOIN W_INTL_ASSGN_D
   ON ...)

That is, the Source Qualifier SQL query was not converted to the Oracle join syntax (SELECT ... FROM ... WHERE ... (+) = ... ) before being executed.

When the Source Qualifier SQL query (with the LEFT OUTER JOINs) was executed directly against the Oracle Database Release 11.2.0.1  in SQL*Plus / SQL Developer / Toad, the following error was thrown:

ORA-03113: end-of-file on communication channel

Whenever an ORA-03113 error is encountered, a trace file is generated under $ORACLE_BASE/diag/rdbms/<db_name>/<instance_name>/trace.  A review of this trace file shows the following additional error:

ORA-07445: exception encountered: core dump [kkqstcrf()+1355] [SIGSEGV] [ADDR:0x71] [PC:0x1D30D3B] [Address not mapped to object] []

ssexhd: crashing the process...

Shadow_Core_Dump = PARTIAL

解决办法：
　　１.Patch 9050716
　　２.临时修改　alter session set "_optimizer_join_elimination_enabled" = false;
　　或者加ｈｉｎｔ：/*+ OPT_PARAM('_optimizer_join_elimination_enabled' 'false') */


最后，修改参数解决。
　　


附：
　　SIL_WorkforceEventFact Fails with ERROR: Prepare failed (Doc ID 1267002.1)               


In this Document
Symptoms
Cause
Solution
References


APPLIES TO:
Human Resources Operations and Compliance Analytics - Version 7.9.6.1 and later
Business Intelligence Suite Enterprise Edition - Version 10.1.3.4.1 [1900] and later
Information in this document applies to any platform.
SYMPTOMS
On: Oracle BI Applications 7.9.6.1 with Oracle Exadata Database 11.2.0.1


SIL_WorkforceEventFact fails and the following Error is thrown in the Informatica Session Log file:
RR_4029 : SQ Instance [Sq_W_WRKFC_EVT_FS] User specified SQL Query [SELECT TAB.ASSIGNMENT_ID, TAB.EFFECTIVE_START_DATE, ... ]


RR_4035 : SQL Error [
Database driver error...
Function Name : Execute
SQL Stmt : SELECT TAB.ASSIGNMENT_ID, ... ]


BLKR_16004 : ERROR: Prepare failed.
CAUSE
This issue is due to a Bug in the Oracle Database:
Bug 9856699 ORA-7445 KKQSTCRF() GENERATING ON A SELECT STATEMENT HAVING LEFT OUTER JOIN


This bug is a duplicate of the unpublished Bug 9050716 ORA-7445 [KKQSTCRF()+1348] [SIGSEGV] DURING EXPLAIN PLAN


As per the Informatica Session Log file for SIL_WorkforceEventFact, the Source Qualifier SQL query is executed with the ANSI Joins "as-is", for example:


SELECT TAB.ASSIGNMENT_ID, TAB.EFFECTIVE_START_DATE, ...
FROM
(SELECT ...
FROM W_WRKFC_EVT_FS A LEFT OUTER JOIN W_WRKFC_EVENT_TYPE_D EVT
   ON EVT.INTEGRATION_ID = A.EVENT_TYPE_RSN_ID AND EVT.DATASOURCE_NUM_ID = A.DATASOURCE_NUM_ID
LEFT OUTER JOIN W_INTL_ASSGN_D
   ON ...)


That is, the Source Qualifier SQL query was not converted to the Oracle join syntax (SELECT ... FROM ... WHERE ... (+) = ... ) before being executed.


When the Source Qualifier SQL query (with the LEFT OUTER JOINs) was executed directly against the Oracle Database Release 11.2.0.1  in SQL*Plus / SQL Developer / Toad, the following error was thrown:
ORA-03113: end-of-file on communication channel


Whenever an ORA-03113 error is encountered, a trace file is generated under $ORACLE_BASE/diag/rdbms/<db_name>/<instance_name>/trace.  A review of this trace file shows the following additional error:




ORA-07445: exception encountered: core dump [kkqstcrf()+1355] [SIGSEGV] [ADDR:0x71] [PC:0x1D30D3B] [Address not mapped to object] []


ssexhd: crashing the process...
Shadow_Core_Dump = PARTIAL
SOLUTION
Please refer to Document 1160193.1 Ora-07445 During Query Execution for the solution.


In the case where Patch 9050716 may not currently be available for a particular Database platform (for example: Oracle Exadata), or it is not desirable to set _optimizer_join_elimination_enabled at the database level, this optimizer parameter can be set at the session or statement level as below:


1.  The parameter can be set at the session level via Informatica PowerCenter, as below:




1.1. Open the Informatica PowerCenter WorkFlow Manager
1.2. From the main menu (at the top), go to Connections > Relational
1.3. Select the Oracle - DataWarehouse connection (Type - Object) and press the "Edit" button
1.4. In the "Connection Object Definition" dialog box, go to the "Connection Environment SQL" attribute and click the down arrow icon in the Value field
1.5. In the "SQL Editor - DataWarehouse (Connection)" dialog box, enter the following in the "SQL" field:
alter session set "_optimizer_join_elimination_enabled" = false;
1.6. Click OK, OK, Close


2.  The parameter can be set at the statement level via Informatica PowerCenter, as below:




2.1.  Open the SILOS > SIL_WorkforceEventFact workflow for editing in PowerCenter Workflow Manager
2.2  Edit the SIL_WorkforceEventFact task / session
2.3  Go to the Mapping tab and click on the SQ Source
2.4  Go to the SQL Query Attribute and add the hint in the Value field:
SELECT /*+ OPT_PARAM('_optimizer_join_elimination_enabled' 'false') */ TAB.ASSIGNMENT_ID,...
2.5  Click OK, OK
2.6  Re-validate the Session and save the Workflow
REFERENCES


BUG:9856699 - ORA-07445 KKQSTCRF GENERATING ON A SELECT STATEMENT HAVING LEFT OUTER JOIN
NOTE:1160193.1 - ORA-07445 [kkqstUnswapOpns] During Query Execution
NOTE:377333.1 - OPT_PARAM Hint

郑全

根本原因：
　　　　That is, the Source Qualifier SQL query was not converted to the Oracle join syntax (SELECT ... FROM ... WHERE ... (+) = ... ) before being executed.

郑全

报警文件内容：

Exception [type: SIGSEGV, Address not mapped to object] [ADDR:0x71] [PC:0x1CBAD19, kkqstcrf()+1355] [flags: 0x0, count: 1]


DDE: Problem Key 'ORA 7445 [kkqstcrf()+1355]' was completely flood controlled (0x6)