Oracle10.2.04 for aix 在收集stats時報ORA-00600 [rworupo.1] 問題的解決辦法

cnhtm發表於2010-01-11

Oracle 10.2.0.4 RAC for aix5.3,在其中一個節點上的警告日誌中報如下錯誤:

Thread 1 advanced to log sequence 8858 (LGWR switch)
Current log# 3 seq# 8858 mem# 0: /dev/rxxx_redo3_01
Current log# 3 seq# 8858 mem# 1: /dev/rxxx_redo3_02
Thu Aug 20 21:30:41 2009
Errors in file /oracle/admin/XXXDB/bdump/xxxdb_j000_679984.trc:
ORA-00600: internal error code, arguments: [rworupo.1], [229], [200], [], [], [], [], []
Thu Aug 20 21:30:46 2009
Errors in file /oracle/admin/XXXXDB/bdump/xxxdb_j000_679984.trc:
ORA-00600: internal error code, arguments: [ORA-00600: internal error code, arguments: [rworupo.1], [229], [200], [], [], [], [], []
ORA-06512: at "SYS.DBMS_STATS", line 19483
], [], [], [], [], [], [], []
Thu Aug 20 23:50:03 2009

檢視 xxxdb_j000_679984.trc 檔案,發現導致問題的語句如下:

[@more@]
select min(minbkt),
maxbkt,
substrb(dump(min(val), 16, 0, 32), 1, 120) minval,
substrb(dump(max(val), 16, 0, 32), 1, 120) maxval,
sum(rep) sumrep,
sum(repsq) sumrepsq,
max(rep) maxrep,
count(*) bktndv,
sum(case
when rep = 1 then
1
else
0
end) unqrep
from (select val,
min(bkt) minbkt,
max(bkt) maxbkt,
count(val) rep,
count(val) * count(val) repsq
from (select /*+ no_parallel(t) no_parallel_index(t) dbms_stats cursor_sharing_exact use_weak_name_resl dynamic_sampling(0) no_monitoring */
substrb("INTERNAL_KEY", 1, 32) val,
ntile(254) over(order by nlssort(substrb("INTERNAL_KEY", 1, 32), 'NLS_SORT = binary')) bkt
from "FSD"."TP_AML_03_01_ALL" t
where substrb("INTERNAL_KEY", 1, 32) is not null)
group by val)
group by maxbkt
order by maxbkt

把這個語句在plsql中直接執行,也會在警告日誌中報ORA-600錯誤。

在metalink中搜尋,發現是一個不過,bug id:4655998

解決的辦法是設定引數 "_newsort_enabled"=false

首先用alter session命令設定這個引數,然後執行上面的語句,發現不再報錯:

SQL> alter session set "_newsort_enabled"=false;

然後在例項級別設定這個引數,以後沒再出現錯誤:

SQL> alter system set "_newsort_enabled"=false scope=both;

--end--

來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/22049049/viewspace-1030458/,如需轉載,請註明出處,否則將追究法律責任。

相關文章