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博客 ” ，链接：https://blog.itpub.net/4227/viewspace-227943/ ，如需转载，请注明出处，否则将追究法律责任。
