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:

Product (Component)	Oracle Server (Rdbms)
Range of versions believed to be affected	Versions >= 11.2 but BELOW 12.1
Versions confirmed as being affected	11.2.0.3 11.2.0.2 11.2.0.1
Platforms affected	Generic (all / most platforms affected)

 It is believed to be a regression in default behaviour 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 - click  to check.

Symptoms:	Related To:
Internal Error May Occur (ORA-600) ORA-600 [rwoirw: check ret val]	Optimizer _optimizer_distinct_agg_transform Optimizer 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

References

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