ORA-7445(kkojnp)错误

查询alert文件，发现ORA-7445 [KKOJNP]错误，出错数据库版本为9204 for Linux X86-64。

 

 

错误信息为：

Errors in file /data/admin/testcen/udump/testcen_ora_14291.trc:
ORA-07445: exception encountered: core dump [kkojnp()+11380] [SIGSEGV] [Address not mapped to object] [0x000000002] [] []

出错SQL信息为：

ORA-07445: exception encountered: core dump [kkojnp()+11380] [SIGSEGV] [Address not mapped to object] [0x000000002] [] []
Current SQL statement for this session:
SELECT /*+ FIRST_ROWS */ *
FROM
(
        SELECT ROWNUM RN, A.*
        FROM
        (
                SELECT  MAX(ROWNUM) OVER() ROWCOUNT,
                        O.RECORD_ID,
                        OI.ORD_ITEM_OOS_STATE
                FROM ORD_ORDER_ITEM O ,ORD_ITEM_OOS OI
                WHERE O.ORDER_ID = OI.ORD_ID
                AND O.ORDER_ID = 'FR20T0000053000000346026'
                AND ROWNUM <= 10
        ) A
)
WHERE RN >= 1

Oracle对这个7445错误给出了三个已知bug的链接，其中一个与星型查询有关，第二个与pushed join predicate有关。当前的情况与二者都不相符。而第三个bug与分析函数有关，而这和当前问题是一致的。

根据这个文档Doc ID:  Note:2913155.8的描述，在SQL中使用分析函数，且SQL不带ORDER BY语句，可能造成这个7445错误。

这个bug确认影响的版本为9204，Oracle在9205和10g中解决了这个bug。

而Oracle给出的临时解决方法是去掉FIRST_ROWS提示。

SQL> EXPLAIN PLAN FOR
  2  SELECT *
  3  FROM
  4  (
  5     SELECT ROWNUM RN, A.*
  6     FROM
  7     ( 
  8             SELECT  MAX(ROWNUM) OVER() ROWCOUNT,
  9                     O.RECORD_ID,
 10                     OI.ORD_ITEM_OOS_STATE
 11             FROM ORD_ORDER_ITEM O ,ORD_ITEM_OOS OI
 12             WHERE O.ORDER_ID = OI.ORD_ID
 13             AND O.ORDER_ID = 'FR20T0000053000000346026'
 14             AND ROWNUM <= 10
 15     ) A
 16  )
 17  WHERE RN >= 1
 18  ;

Explained.

SQL> EXPLAIN PLAN FOR
  2  SELECT /*+ FIRST_ROWS */ *
  3  FROM
  4  (
  5     SELECT ROWNUM RN, A.*
  6     FROM
  7     ( 
  8             SELECT  MAX(ROWNUM) OVER() ROWCOUNT,
  9                     O.RECORD_ID,
 10                     OI.ORD_ITEM_OOS_STATE
 11             FROM ORD_ORDER_ITEM O ,ORD_ITEM_OOS OI
 12             WHERE O.ORDER_ID = OI.ORD_ID
 13             AND O.ORDER_ID = 'FR20T0000053000000346026'
 14             AND ROWNUM <= 10
 15     ) A
 16  )
 17  WHERE RN >= 1
 18  ;
                AND O.ORDER_ID = 'FR20T0000053000000346026'
        *
ERROR at line 13:
ORA-03113: end-of-file on communication channel

Oracle给出的方法果然有效，而且这个bug似乎可以重现，下面通过构造测试表，看能否重现bug：

SQL> CREATE TABLE T AS SELECT * FROM ALL_OBJECTS WHERE WNER = 'SYS';

Table created.

SQL> CREATE TABLE T1 AS SELECT * FROM ALL_TABLES WHERE WNER = 'SYS';

Table created.

SQL> COL OBJECT_NAME FORMAT A30
SQL> SELECT /*+ FIRST_ROWS */ *
  2  FROM
  3  (
  4     SELECT  MAX(ROWNUM) OVER() ROWCOUNT,
  5             TABLE_NAME,
  6             OBJECT_NAME
  7     FROM T, T1
  8     WHERE T.OBJECT_NAME = T1.TABLE_NAME 
  9     AND ROWNUM <= 5
 10  )
 11  ;

  ROWCOUNT TABLE_NAME                     OBJECT_NAME
---------- ------------------------------ ------------------------------
         5 AUDIT_ACTIONS                  AUDIT_ACTIONS
         5 DUAL                           DUAL
         5 ODCI_SECOBJ$                   ODCI_SECOBJ$
         5 ODCI_WARNINGS$                 ODCI_WARNINGS$
         5 PLAN_TABLE                     PLAN_TABLE

问题没有重现，考虑到FIRST_ROWS提示是导致bug产生的条件之一，说明很可能是索引扫描导致的问题，为T1表添加索引，再次尝试查询：

SQL> CREATE INDEX IND_T1_NAME ON T1(TABLE_NAME);

Index created.

SQL> SELECT /*+ FIRST_ROWS */ *
  2  FROM
  3  (
  4     SELECT  MAX(ROWNUM) OVER() ROWCOUNT,
  5             TABLE_NAME,
  6             OBJECT_NAME
  7     FROM T, T1
  8     WHERE T.OBJECT_NAME = T1.TABLE_NAME 
  9     AND ROWNUM <= 5
 10  )
 11  ;
FROM
 *
ERROR at line 2:
ORA-03113: end-of-file on communication channel

问题果然重现，看来问题还与索引扫描有关。那么是否不加FIRST_ROWS而使用INDEX提示一样可以导致问题重现呢：

SQL> CONN TEST
Enter password:
Connected.
SQL> SELECT /*+ INDEX(T1) */ *
  2  FROM
  3  (
  4     SELECT  MAX(ROWNUM) OVER() ROWCOUNT,
  5             TABLE_NAME,
  6             OBJECT_NAME
  7     FROM T, T1
  8     WHERE T.OBJECT_NAME = T1.TABLE_NAME 
  9     AND ROWNUM <= 5
 10  )
 11  ;

  ROWCOUNT TABLE_NAME                     OBJECT_NAME
---------- ------------------------------ ------------------------------
         5 AUDIT_ACTIONS                  AUDIT_ACTIONS
         5 DUAL                           DUAL
         5 ODCI_SECOBJ$                   ODCI_SECOBJ$
         5 ODCI_WARNINGS$                 ODCI_WARNINGS$
         5 PLAN_TABLE                     PLAN_TABLE

问题没有重现，那么将整个优化模式改为FIRST_ROWS会怎样：

SQL> ALTER SESSION SET OPTIMIZER_MODE = FIRST_ROWS;

Session altered.

SQL> SELECT /*+ INDEX(T1) */ *
  2  FROM
  3  (
  4     SELECT  MAX(ROWNUM) OVER() ROWCOUNT,
  5             TABLE_NAME,
  6             OBJECT_NAME
  7     FROM T, T1
  8     WHERE T.OBJECT_NAME = T1.TABLE_NAME 
  9     AND ROWNUM <= 5
 10  )
 11  ;
SELECT /*+ INDEX(T1) */ *
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel

这次问题重现了，那么将提示改为FULL会如何：

SQL> CONN TEST
Enter password:
Connected.
SQL> ALTER SESSION SET OPTIMIZER_MODE = FIRST_ROWS;

Session altered.

SQL> SELECT /*+ FULL(T1) */ *
  2  FROM
  3  (
  4     SELECT  MAX(ROWNUM) OVER() ROWCOUNT,
  5             TABLE_NAME,
  6             OBJECT_NAME
  7     FROM T, T1
  8     WHERE T.OBJECT_NAME = T1.TABLE_NAME 
  9     AND ROWNUM <= 5
 10  )
 11  ;
SELECT /*+ FULL(T1) */ *
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel

看来问题和Oracle选择的执行计划并没有关系，前面在EXPLAIN PLAN FOR的过程中报错也说明了问题发生在CBO的分析阶段，而不是执行阶段。那么可以推断是由于CBO的FIRST_ROWS模式在分析索引扫描时出现的问题。

SQL> CONN TEST
Enter password:
Connected.
SQL> SELECT /*+ FIRST_ROWS */ *
  2  FROM
  3  (
  4     SELECT  MAX(ROWNUM) OVER(ORDER BY TABLE_NAME) ROWCOUNT,
  5             TABLE_NAME,
  6             OBJECT_NAME
  7     FROM T, T1
  8     WHERE T.OBJECT_NAME = T1.TABLE_NAME 
  9     AND ROWNUM <= 5
 10  )
 11  ;
SELECT /*+ FIRST_ROWS */ *
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel

SQL> CONN TEST
Enter password:
Connected.
SQL> SELECT /*+ FIRST_ROWS */ *
  2  FROM
  3  (
  4     SELECT  MAX(ROWNUM) OVER() ROWCOUNT,
  5             TABLE_NAME,
  6             OBJECT_NAME
  7     FROM T, T1
  8     WHERE T.OBJECT_NAME = T1.TABLE_NAME 
  9     AND ROWNUM <= 5
 10     ORDER BY TABLE_NAME
 11  )
 12  ;

  ROWCOUNT TABLE_NAME                     OBJECT_NAME
---------- ------------------------------ ------------------------------
         5 AUDIT_ACTIONS                  AUDIT_ACTIONS
         5 DUAL                           DUAL
         5 ODCI_SECOBJ$                   ODCI_SECOBJ$
         5 ODCI_WARNINGS$                 ODCI_WARNINGS$
         5 PLAN_TABLE                     PLAN_TABLE

SQL> SELECT /*+ FIRST_ROWS */ *
  2  FROM
  3  (
  4     SELECT  TABLE_NAME,
  5             OBJECT_NAME
  6     FROM T, T1
  7     WHERE T.OBJECT_NAME = T1.TABLE_NAME 
  8     AND ROWNUM <= 5
  9  )
 10  ;

TABLE_NAME                     OBJECT_NAME
------------------------------ ------------------------------
AUDIT_ACTIONS                  AUDIT_ACTIONS
DUAL                           DUAL
ODCI_SECOBJ$                   ODCI_SECOBJ$
ODCI_WARNINGS$                 ODCI_WARNINGS$
PLAN_TABLE                     PLAN_TABLE

最后对比上面三个查询，可以推断，是否产生问题与SQL语句的ORDER BY语句有关，而与分析函数的ORDER BY语句没有关系。此外分析函数也是导致问题的主要原因之一。

 

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

转载于:http://blog.itpub.net/4227/viewspace-227943/