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

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

問題SQL：

SELECTSUM(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 ANDa.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_codeAND 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))

官方：

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

Bug12947671ora-600 [rwoirw: check ret val] with count distinct andorder by

改善建議：

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


官方说明：
Bug 12947671ora-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
Clickherefor details of each of the sections below.

Affects:

Product (Component)

Oracle Server (Rdbms)

Range of versionsbelievedto be affected

Versions >= 11.2 but BELOW 12.1

Versionsconfirmedas being affected

11.2.0.311.2.0.211.2.0.1

Platforms affected

Generic (all / most platforms affected)

It is believed to be aregressionindefaultbehaviour thus:
Regression introduced in 11.2.0.1

Fixed:

The fix for 12947671 is first included in

12.1.0.1 (Base Release)11.2.0.4 (Server Patch Set)11.2.0.3 Patch 29 on Windows Platforms

Interim patches may be available for earlier versions - clickhereto check.

Symptoms:

Related To:

Internal Error May Occur (ORA-600)ORA-600 [rwoirw: check ret val]Optimizer_optimizer_distinct_agg_transformOptimizer Feature DAT

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

Please note:The above is a summary description only. Actual symptoms can vary. Matching to any symptoms here does not confirm that you are encountering this problem. For questions about this bug please consult Oracle Support.

References

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