Oracle10.2.04 for aix 在收集stats时报ORA-00600 [rworupo.1] 问题的解决办法

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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/22049049/viewspace-1030458/