ORA-00600:[32695], [hash aggregation can't be done]错误一例

还是那个 hash group by算法的问题 ，日志文件中出现以下记录:

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73

***  ACTION  NAME :(SQL 窗口 - 新建) 2010-09-03 14:27:54.594 *** MODULE  NAME :(PL/SQL Developer) 2010-09-03 14:27:54.594 *** SERVICE  NAME :(HQYDB1) 2010-09-03 14:27:54.594 *** SESSION ID:(3205.17923) 2010-09-03 14:27:54.594 *** 2010-09-03 14:27:54.594 ksedmp: internal  or  fatal error ORA-00600: internal error code, arguments: [32695], [hash aggregation can 't be done], [], [], [], [], [], [] Current SQL statement for this session: create table zou_201008_cell_id as select /* g_all_cdr02,60 */ calling_num mobile_number, lac, lpad(cell_id,5,' 0 ') cell_id, count(*) c, sum(call_duration) call_duration, sum(decode(record_type,' 00 ',1,0)*call_duration) moc_call_duration, sum(decode(record_type,' 01 ',1,0)*call_duration) mtc_call_duarion from  g_all_cdr02 where substr(calling_num,1,7) in (select mobile_prefix from zou_mobile_prefix) group by calling_num , lac, lpad(cell_id,5,' 0') ----- Call Stack Trace ----- calling              call     entry                argument  values  in  hex location             type     point                (? means dubious value) -------------------- -------- -------------------- ---------------------------- ksedst+001c          bl       ksedst1              100000000 ? 11055A9A0 ? ksedmp+0290          bl       ksedst               104A54870 ? ksfdmp+0018          bl       03F30204 kgeriv+0108          bl       _ptrgl kgesiv+0080          bl       kgeriv               1050BE654 ? 1050BE604 ?                                                     0000027E5 ? 080000000 ?                                                     07FFFFFFF ? ksesic1+0060         bl       kgesiv               43300000FFFF5310 ?                                                     4530000000000000 ?                                                     000000071 ? 000000001 ?                                                     000000000 ? qeshPartitionBuildH  bl       01F9CA24 D+04bc qeshGBYOpenScan2+02  bl       qeshPartitionBuildH  0000027E5 ? 1105C06C0 ? 34                            D qeshGBYOpenScan+001  bl       qeshGBYOpenScan2     FFFFFFFFFFF5740 ? 11055A938 ? 8                                                  000000000 ? 000000010 ? qerghFetch+05e8      bl       qeshGBYOpenScan      000001000 ? rwsfcd+0054          bl       _ptrgl qerltFetch+036c      bl       03F2EB1C ctcdrv+4160          bl       01F9C898 opiexe+2884          bl       ctcdrv               100000001 ? 100000001 ?                                                     110467F30 ? opiosq0+19f0         bl       opiexe               FFFFFFFFFFF8B50 ?                                                     2824422142420820 ?                                                     FFFFFFFFFFF8C10 ? kpooprx+0168         bl       opiosq0              300000000 ? 000000000 ?                                                     000000000 ? A4000000000000 ? kpoal8+0400          bl       kpooprx              FFFFFFFFFFFB464 ?                                                     FFFFFFFFFFFB068 ?                                                     1BF000001BF ? 100000001 ?                                                     000000000 ? A40000000000A4 ?                                                     000000000 ? 1103878F8 ? opiodr+0ae0          bl       _ptrgl ttcpip+1020          bl       _ptrgl opitsk+1124          bl       01F9F2A0 opiino+0990          bl       opitsk               000000000 ? 000000000 ? opiodr+0ae0          bl       _ptrgl opidrv+0484          bl       01F9E0E8 sou2o+0090           bl       opidrv               3C02DC1BBC ? 44065F000 ?                                                     FFFFFFFFFFFF3A0 ? opimai_real+01bc     bl       01F9B9F4 main+0098            bl       opimai_real          000000000 ? 000000000 ? __start+0098         bl       main                 000000000 ? 000000000 ?   --------------------- Binary Stack Dump ---------------------

这次是因为应用人员不了解alter session的作用域，在PL/SQL Developer工具中的不同窗口(也就是不在同一会话中)中执行了"alter session set "_gby_hash_aggregation_enabled" = false;"和涉及group by操作的SQL，并导致了unpublished bug:6471770被触发。 我们比较容易地workaround绕过这个Bug:

?

1 2 3 4 5 6 7

/* 在会话级别设置优化参数_gby_hash_aggregation_enabled */   alter  session  set  "_gby_hash_aggregation_enabled"  =  false ;   /* 或者在语句中加入NO_USE_HASH_AGGREGATION的 hint */   select   /*+ NO_USE_HASH_AGGREGATION */ ....

以上提及的unpublished bug:6471770据称在10.2.0.5,11.1.0.7,11.2.0.1版本中被修正了。

本文转自maclean_007 51CTO博客，原文链接:http://blog.51cto.com/maclean/1277548