查询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 OWNER = 'SYS';

Table created.

SQL> CREATE TABLE T1 AS SELECT * FROM ALL_TABLES WHERE OWNER = '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语句没有关系。此外分析函数也是导致问题的主要原因之一。