ora-600 [rwoirw: check ret val] with count distinct and order by

點檢DB看到web程式引發 ORA-00600[rwoirw: check ret val]

Mon Mar 02 09:32:06 2015 ORA-00600: 内部错误代码, 参数: [rwoirw: check ret val]

問題SQL：

SELECT SUM(MD01)MD01,SUM(MD02)MD02,SUM(MD03)MD03,SUM(MD04)MD04,SUM(MD05)MD05,SUM(MD06)MD06,SUM(MD07)MD07,SUM(MD08A)MD08A,SUM(MD08B)MD08B,SUM(MD09)MD09,
SUM(MD10)MD10,SUM(MD11)MD11,SUM(MD12)MD12,SUM(MD13)MD13,SUM(MD14)MD14 FROM (select case when line_desc ='MD01' THEN QTY ELSE 0 END MD01,
case when line_desc ='MD02' THEN QTY ELSE 0 END MD02, case when line_desc ='MD03' THEN QTY ELSE 0 END MD03,
case when line_desc ='MD04' THEN QTY ELSE 0 END MD04, case when line_desc ='MD05' THEN QTY ELSE 0 END MD05,
case when line_desc ='MD06' THEN QTY ELSE 0 END MD06, case when line_desc ='MD07' THEN QTY ELSE 0 END MD07,
case when line_desc ='MD08A' THEN QTY ELSE 0 END MD08A, case when line_desc ='MD08B' THEN QTY ELSE 0 END MD08B,
case when line_desc ='MD09' THEN QTY ELSE 0 END MD09, case when line_desc ='MD10' THEN QTY ELSE 0 END MD10,
case when line_desc ='MD11' THEN QTY ELSE 0 END MD11, case when line_desc ='MD12' THEN QTY ELSE 0 END MD12,
case when line_desc ='MD13' THEN QTY ELSE 0 END MD13, case when line_desc ='MD14' THEN QTY ELSE 0 END MD14
from ( select nvl(e.qty,0)QTY,f.line_desc from ( SELECT /*+index(a r105_1)*/ COUNT(distinct b.mo_number)qty, D.LINE_DESC
FROM sfism4.r_mo_base_t a, sfism4.r_wip_tracking_t b, sfis1.c_route_control_t c, C_LINE_DESC_T D
WHERE a.mo_number = b.mo_number AND b.line_name = D.LINE_NAME AND a.close_flag <> '3'
AND c.group_next NOT IN ('0', 'PACKING', 'SHIPPING', 'OBE', 'OUT STORE', 'SCRAP')
AND b.group_name = c.group_name AND b.special_route = c.route_code AND b.error_flag = c.state_flag AND b.in_line_time < SYSDATE - '5'
AND a.mo_create_date >= SYSDATE - 90 GROUP BY D.LINE_DESC ORDER BY LINE_DESC)e,
(select distinct LINE_DESC from C_LINE_DESC_T where LINE_DESC not in ('N/A','RM01','M200') order by LINE_DESC) f
where e.line_desc(+) = f.line_desc order by f.line_desc))

官方：

Oracle 11.2.0.1-11.2.0.3 版本，SQL子查詢中使用count ,distinct 和order by 聯用會引發

Bug 12947671 ora-600 [rwoirw: check ret val] with count distinct and order by

改善建議：

將一個括號中子查詢e 中的 ORDER BY LINE_DESC 去掉，且不會影響查詢結果。


官方说明：
Bug 12947671 ora-600 [rwoirw: check ret val] with count distinct and order by

This note gives a brief overview of bug 12947671.
The content was last updated on: 13-OCT-2014
Click here for details of each of the sections below.

Affects:

It is believed to be a regression in default behaviour thus:
Regression introduced in 11.2.0.1

Fixed:

Interim patches may be available for earlier versions - click here to check.

Description

it was possible to get an internal error [rwoirw: check ret val] for a query with order by clause and distinct aggregation.

eg:

SELECT count(count(DISTINCT deptno))

FROM emp Y

GROUP BY Y.deptno

ORDER BY Y.deptno;

Workaround

"_optimizer_distinct_agg_transform"= FALSE

HOOKS "OERI:rwoirw: check ret val" PARAMETER:_optimizer_distinct_agg_transform CBO:DAT ORA-600 [rwoirw: check ret val] PARAMETER:_optimizer_distinct_agg_transform CBO:DAT LIKELYAFFECTS XAFFECTS_11.2.0.1 XAFFECTS_V11020001 AFFECTS=11.2.0.1 XAFFECTS_11.2.0.2 XAFFECTS_V11020002 AFFECTS=11.2.0.2 XAFFECTS_11.2.0.3 XAFFECTS_V11020003 AFFECTS=11.2.0.3 XPRODID_5 PRODUCT_ID=5 PRODID-5 RDBMS XCOMP_RDBMS COMPONENT=RDBMS TAG_CBO TAG_OERI TAG_RB201 CBO OERI RB201 FIXED_11.2.0.4 FIXED_12.1.0.1 FIXED_WIN:B203P29

References

Bug:12947671 (This link will only work for PUBLISHED bugs)
Note:245840.1 Information on the sections in this article