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 here 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

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.1 Information on the sections in this article

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

转载于:http://blog.itpub.net/25583515/viewspace-2146383/