點檢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 | |
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 |
Interim patches may be available for earlier versions - click here to check.
Symptoms: | Related To: |
|
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/