ORA-7445(kkodsel)错误

今天又碰到一个bug，还是ORA-7445错误。

在数据库中运行下面的SQL得到ORA-3113错误：

SQL> SELECT TT.CITY,
2 TT.PRODUCT_ID,
3 TT.PRICE20051,
4 TT.NUMBER20051
5 FROM
6 (
7 SELECT TT.CITY,
8 TT.PRODUCT_ID,
9 ROUND(SUM(DECODE(TT.QQ,'20051',TT.PRICE/TT.NUM,NULL)),8) PRICE20051,
10 SUM(DECODE(TT.QQ,'20051',TT.NUM,NULL)) NUMBER20051
11 FROM
12 (
13 SELECT PLAT_ID CITY,
14 TO_CHAR(T.CREATE_DATE,'YYYYQ') QQ,
15 T.PRODUCT_ID,
16 SUM(T.UNIT_PRICE*T.REQUEST_QTY) PRICE,
17 SUM(T.REQUEST_QTY) NUM
18 FROM JY_TMP_YIYAO T
19 WHERE NVL(T.UNIT_PRICE*T.REQUEST_QTY,0) >0
20 GROUP BY T.PLAT_ID, TO_CHAR(T.CREATE_DATE,'YYYYQ'), T.PRODUCT_ID
21 ) TT
22 WHERE TT.NUM > 0
23 GROUP BY TT.CITY, TT.PRODUCT_ID
24 ) TT,
25 (SELECT P.ID FROM COM_PRODUCT_0226 P WHERE SUBSTR(P.PRODUCT_CODE,1,2) = '40') P,
26 (
27 SELECT AR.PRODUCT_ID
28 FROM CAT_AREA_PRICE AR
29 WHERE AR.PRICE_TYPE = '1'
30 AND AR.PLAT_ID = (SELECT ID FROM PLT_PLAT P WHERE P.PLAT_NAME = '中心')
31 ) AR
32 WHERE TT.PRODUCT_ID = P.ID
33 AND P.ID = AR.PRODUCT_ID
34 ;
ROUND(SUM(DECODE(TT.QQ,'20051',TT.PRICE/TT.NUM,NULL)),8) PRICE20051,
*
ERROR at line 9:
ORA-03113: end-of-file on communication channel

而后台alert文件中出现了ORA-7445错误：

ksedmp: internal or fatal error
ORA-07445: exception encountered: core dump [0000000100E84B7C] [SIGSEGV] [Address not mapped to object] [0x0000004F0] [] []

其中第一个出错函数为：kkodsel，根据信息在Metalink上发现这是一个已经发布的bug信息：Note:2475995.8： Bug 2475995 Dump (in kkodsel) from STAR TRANSFORMATION with view merging。

确认受影响版本就是9204。

简单的说就是由于在启用了星型转换后对视图merging出现的问题。

解决方法是采用NO_MERGE提示来避免视图的MERGE：

SQL> SET AUTOT TRACE
SQL> SELECT /*+ NO_MERGE(TT) */ TT.CITY,
2 TT.PRODUCT_ID,
3 TT.PRICE20051,
4 TT.NUMBER20051
5 FROM
6 (
7 SELECT TT.CITY,
8 TT.PRODUCT_ID,
9 ROUND(SUM(DECODE(TT.QQ,'20051',TT.PRICE/TT.NUM,NULL)),8) PRICE20051,
10 SUM(DECODE(TT.QQ,'20051',TT.NUM,NULL)) NUMBER20051
11 FROM
12 (
13 SELECT PLAT_ID CITY,
14 TO_CHAR(T.CREATE_DATE,'YYYYQ') QQ,
15 T.PRODUCT_ID,
16 SUM(T.UNIT_PRICE*T.REQUEST_QTY) PRICE,
17 SUM(T.REQUEST_QTY) NUM
18 FROM JY_TMP_YIYAO T
19 WHERE NVL(T.UNIT_PRICE*T.REQUEST_QTY,0) >0
20 GROUP BY T.PLAT_ID, TO_CHAR(T.CREATE_DATE,'YYYYQ'), T.PRODUCT_ID
21 ) TT
22 WHERE TT.NUM > 0
23 GROUP BY TT.CITY, TT.PRODUCT_ID
24 ) TT,
25 (SELECT P.ID FROM COM_PRODUCT_0226 P WHERE SUBSTR(P.PRODUCT_CODE,1,2) = '40') P,
26 (
27 SELECT AR.PRODUCT_ID
28 FROM CAT_AREA_PRICE AR
29 WHERE AR.PRICE_TYPE = '1'
30 AND AR.PLAT_ID = (SELECT ID FROM PLT_PLAT P WHERE P.PLAT_NAME = '中心')
31 ) AR
32 WHERE TT.PRODUCT_ID = P.ID
33 AND P.ID = AR.PRODUCT_ID
34 ;

8231 rows selected.

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2476 Card=1 Bytes=19
1)

1 0 NESTED LOOPS (Cost=2476 Card=1 Bytes=191)
2 1 NESTED LOOPS (Cost=2475 Card=1 Bytes=139)
3 2 VIEW (Cost=2472 Card=1 Bytes=78)
4 3 SORT (GROUP BY) (Cost=2472 Card=1 Bytes=78)
5 4 VIEW (Cost=2472 Card=1 Bytes=78)
6 5 FILTER
7 6 SORT (GROUP BY) (Cost=2472 Card=1 Bytes=61)
8 7 TABLE ACCESS (FULL) OF 'JY_TMP_YIYAO' (Cost=1896 Card=112462 Bytes=6860182)
9 2 TABLE ACCESS (BY INDEX ROWID) OF 'COM_PRODUCT_0226' (Cost=3 Card=1 Bytes=61)
10 9 INDEX (RANGE SCAN) OF 'INX_COM_PRODUCT_0226_ID' (NON-UNIQUE) (Cost=2 Card=1)
11 1 TABLE ACCESS (BY INDEX ROWID) OF 'CAT_AREA_PRICE' (Cost=1 Card=1 Bytes=52)
12 11 INDEX (UNIQUE SCAN) OF 'PK_CAT_AREA_PRICE' (UNIQUE)
13 12 TABLE ACCESS (FULL) OF 'PLT_PLAT' (Cost=3 Card=1 Bytes=37)

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
161321 consistent gets
4500 physical reads
0 redo size
389474 bytes sent via SQL*Net to client
6683 bytes received via SQL*Net from client
550 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
8231 rows processed

问题解决。

当然，修改SQL的写法以及设置初始化参数star_transformation_enabled为FALSE等方法也可以解决这个问题。

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

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