OS操作系统：WINDOWS
数据库版 本：ORACLE 10.2.0.4
4月7日客户发邮件统计系统数据库无法导出数据报ORA-00600错误，经过分析诊断该
问题是数据库在执行 HASH GROUP 操作时触发了 bug 6471770。
1、根据数据库报警日志确认问题：
Fri Feb 05 15:04:34 2016
Errors in file d:\oracle\product\10.2.0\admin\hkstat\udump\olmstat_ora_4604.trc:
ORA-00600: 内部错误代码, 参数: [32695], [hash aggregation can't be done], [], [], [], [], [], []
从上面的信息，我们看你到数据库报了ORA-00600错误，下面分析TRACE 文件定位问题原因。
2. olmstat_ora_4604.trc TRACE 文件分析
Dump file d:\oracle\product\10.2.0\admin\hkstat\udump\olmstat_ora_4604.trc
Fri Feb 05 15:04:34 2016
ORACLE V10.2.0.4.0 - 64bit Production vsnsta=0
vsnsql=14 vsnxtr=3
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Windows NT Version V6.0 Service Pack 2
CPU : 16 - type 8664, 8 Physical Cores
Process Affinity : 0x0000000000000000
Memory (Avail/Total): Ph:121M/32755M, Ph+PgF:51612M/65653M
Instance name: olmstat
Redo thread mounted by this instance: 1
Oracle process number: 40
Windows thread id: 4604, image: ORACLE.EXE (SHAD)
*** ACTION NAME:() 2016-02-05 15:04:34.347
*** MODULE NAME:(统计.exe) 2016-02-05 15:04:34.347
*** SERVICE NAME:(olmstat) 2016-02-05 15:04:34.347
*** SESSION ID:(29.977) 2016-02-05 15:04:34.347
*** 2016-02-05 15:04:34.347
ksedmp: internal or fatal error
ORA-00600: 内部错误代码, 参数: [32695], [hash aggregation can't be done], [], [], [], [], [], []
Current SQL statement for this session:
SELECT sum(OLM_CURRENCY), V_MER_NAME V_TBL_STAT WHERE ....
group by V_MER_NAME DESC <<< 此处为了不泄漏客户信息我把SQL语句修改了，客户原来执行的是条很复杂的SQL
----- Call Stack Trace -----
calling call entry argument values in hex
location type point (? means dubious value)
-------------------- -------- -------------------- ----------------------------
ksedmp+663 CALL??? ksedst+55 003C878B8 000000000 0179B8588
000000000
ksfdmp+19 CALL??? ksedmp+663 000000003 027DFD650 01467AB78
003CACC80
kgeriv+184 CALL??? ksfdmp+19 000000000 000000004 000069246
00001E700
kgesiv+102 CALL??? kgeriv+184 000066666 027DFD010 000000000
000000000
ksesic1+125 CALL??? kgesiv+102 000000000 016301250 016340C88
005542F83
qeshPartitionBuildH CALL??? ksesic1+125 000007FB7 000000001 00000001E
D+919 0043F692C
qeshGBYGetNextRow+7 CALL??? qeshPartitionBuildH 000000200 262341578 00000005E
37 D+919 000000000
qerghFetch+284 CALL??? qeshGBYGetNextRow+7 000004000 016343B48
37 7FF07D54080 0016671C6
qervwFetch+142 CALL??? qerghFetch+284 7301010002 1E00010065E01
B400000234 B400000017
rwsfcd+109 CALL??? qervwFetch+142 2626E1020 000007FFF 015DE7C58
000000000
qerhjFetch+2408 CALL??? rwsfcd+109 000000000 077303D62 000000000
000000000
qersoFetch+5516 CALL??? qerhjFetch+2408 57E1F21800000000 7FF0A54D758
7FF134FD5A0 7FF117CBBD0
kpofchswcbk+58 CALL??? qersoFetch+5516 000000000 000000000 000000000
00001F240
rpiswu2+517 CALL??? kpofchswcbk+58 0DCAA00AF 7FFF8C24267
7FF12863E40 003CACC80
kpofrws+488 CALL??? rpiswu2+517 17304B368 00000003E 0179BAA40
000000002
opifch2+5813 CALL??? kpofrws+488 000000000 0000004E6 000000000
000000000
opiall0+4140 CALL??? opifch2+5813 4C44282000000089 000000005
0179BB3B0 7FF10D70630
opial7+549 CALL??? opiall0+4140 00000003E 000000022 0179BB770
0179BE848
opiodr+1136 CALL??? opial7+549 000000001 0179BCA9B 000000000
014681F08
ttcpip+5146 CALL??? opiodr+1136 000000047 00000000F 0179BE848
5B7900000006
opitsk+1818 CALL??? ttcpip+5146 027E12130 00000007F 000000000
000000000
opiino+1129 CALL??? opitsk+1818 000000000 000000000 000000000
000000000
opiodr+1136 CALL??? opiino+1129 00000003C 000000004 0179BFB00
000000000
opidrv+815 CALL??? opiodr+1136 00000003C 000000004 0179BFB00
000000000
sou2o+52 CALL??? opidrv+815 00000003C 000000004 0179BFB00
7FEFF747C54
opimai_real+131 CALL??? sou2o+52 000000000 07763C187 0179BFC20
000000000
opimai+96 CALL??? opimai_real+131 7FFFFF7B258 0179BFCA0
0179BFC00 7FE00000038
OracleThreadStart+6 CALL??? opimai+96 000000000 003C874C4 000000050
40 00000268C
00000000772AA55D CALL??? OracleThreadStart+6 0163DFF1C 000000000 000000000
40 000000000
0000000077616731 CALL??? 00000000772AA55D 000000000 000000000 000000000
000000000
.....
--------------------- Binary Stack Dump ---------------------
Argument/Register addr=0x00000000043F692C.
Dump of memory from 0x00000000043F68EC to 0x00000000043F6A2C
0043F68E0 75716572 [requ]
0043F68F0 64657269 25203D20 00000A64 75632020 [ired = %d... cu]
0043F6900 6E657272 73232074 73746F6C 25203D20 [rrent #slots = %]
0043F6910 00000A64 616D2020 756D6978 7323206D [d... maximum #s]
0043F6920 73746F6C 25203D20 00000A64 68736168 [lots = %d...hash]
0043F6930 67676120 61676572 6E6F6974 6E616320 [ aggregation can]
0043F6940 62207427 6F642065 0000656E 20202020 ['t be done.. ]
0043F6950 68736148 72694420 6F746365 73656972 [Hash Directories]
0043F6960 69756220 7320746C 65636375 75667373 [ built successfu]
0043F6970 21796C6C 00000A21 68736571 654E4849 [lly!!...qeshIHNe]
0043F6980 6F4E7478 706D456E 6B427974 00312E74 [xtNonEmptyBkt.1.]
0043F6990 68736571 78637320 00000000 68736571 [qesh scx....qesh]
0043F69A0 7A697320 00000065 68736571 66756220 [ size...qesh buf]
0043F69B0 00000070 68736571 6F6C6C41 74694263 [p...qeshAllocBit]
0043F69C0 74636556 312E726F 00000000 68736571 [Vector.1....qesh]
0043F69D0 70627320 00000000 68736571 736F6C43 [ sbp....qeshClos]
0043F69E0 61635365 00332E6E 68736571 4270614D [eScan.3.qeshMapB]
0043F69F0 65567469 726F7463 0000312E 68736571 [itVector.1..qesh]
0043F6A00 704F4448 63536E65 312E6E61 00000000 [HDOpenScan.1....]
0043F6A10 69442020 62206B73 64657361 20444820 [ Disk based HD ]
0043F6A20 6E616373 65706F20 0A64656E [scan opened.]
Argument/Register addr=0x000007FF117CBBD0.
Dump of memory from 0x000007FF117CBB90 to 0x000007FF117CBCD0
7FF117CBB90 0A54D680 000007FF 00000001 00010001 [..T.............]
7FF117CBBA0 00000000 00000002 00000000 00000000 [................]
7FF117CBBB0 00000001 00000000 00000002 00000000 [................]
7FF117CBBC0 00000000 00000000 00000000 00000000 [................]
7FF117CBBD0 BBC8209B DDD8CE9E B23B38EA 506DBDF9 [. .......8;...mP]
7FF117CBBE0 00000000 02747801 19100F05 00000000 [.....xt.........]
7FF117CBBF0 00000000 00000000 000000DA 00000000 [................]
7FF117CBC00 00000000 00000000 00000000 00000000 [................]
7FF117CBC10 117CBC20 000007FF 00000000 00000000 [ .|.............]
7FF117CBC20 69676562 4B50206E 45525F47 54524F50 [begin PKG_REPORT]
7FF117CBC30 5349525F 70732E4B 72656D5F 6E616863 [_RISK.sp_merchan]
7FF117CBC40 72745F74 28736E61 49474542 41445F4E [t_trans(BEGIN_DA]
7FF117CBC50 3E3D4554 31303227 30313035 202C2731 [TE=>'20150101', ]
7FF117CBC60 5F444E45 45544144 32273E3D 31353130 [END_DATE=>'20151]
7FF117CBC70 27313332 4150202C 415F5143 3D414552 [231', PACQ_AREA=]
7FF117CBC80 2323273E 2C272323 43415020 4E495F51 [>'####', PACQ_IN]
7FF117CBC90 4F435F53 3E3D4544 23232327 23232323 [S_CODE=>'#######]
7FF117CBCA0 23232323 2C272323 45525020 415F5643 [######', PRECV_A]
7FF117CBCB0 3D414552 2323273E 2C272323 45525020 [REA=>'####', PRE]
7FF117CBCC0 495F5643 435F534E 3D45444F 2323273E [CV_INS_CODE=>'##]
Argument/Register addr=0x000000000001F240.
Dump of memory from 0x000000000001F200 to 0x000000000001F340
00001F200 00000000 00000000 00000000 00000000 [................]
Repeat 19 times
Argument/Register addr=0x00000000DCAA00AF.
Dump of memory from 0x00000000DCAA006F to 0x00000000DCAA01AF
0DCAA0060 03520418 [..R.]
0DCAA0070 00000352 0418005A 04B30465 054F0502 [R...Z...e.....O.]
0DCAA0080 05EC059F 0689063B 072606D8 07C40776 [....;.....&.v...]
0DCAA0090 085E0811 08FB08AB 09970948 0A3209E4 [..^.....H.....2.]
0DCAA00A0 0AD30A82 0B6F0B20 0C0C0BBE 0CA80C5A [.... .o.....Z...]
0DCAA00B0 0D450CF6 0DE20D93 0E800E31 0F1C0ECE [..E.....1.......]
0DCAA00C0 0FB60F6A 10521004 10EE109F 118A113D [j.....R.....=...]
0DCAA00D0 122611D9 12C41275 13601311 13FC13AE [..&.u.....`.....]
0DCAA00E0 14981449 153414E5 15D11582 166D161F [I.....4.......m.]
0DCAA00F0 170916BA 17A71757 184317F4 18DE1892 [....W.....C.....]
0DCAA0100 197C192E 1A1819CB 1AB21A66 1B4C1AFF [..|.....f.....L.]
0DCAA0110 1BEC1B9B 1C891C3A 1D271CD7 1DC21D74 [....:.....'.t...]
0DCAA0120 1E5E1E10 1EFD1EAF 001C1F4A 0001006E [..^.....J...n...]
0DCAA0130 00052AA0 00052AA0 00000007 00000000 [.*...*..........]
0DCAA0140 4210160A F9890000 00000102 22C157D0 [...B.........W."]
0DCAA0150 0040D781 00210203 2B05636B 35C39330 [..@...!.kc.+0..5]
0DCAA0160 0531CF23 00000000 00000002 00370037 [#.1.........7.7.]
0DCAA0170 31303208 33313036 38300D31 38393734 [.20160131.084798]
0DCAA0180 34343330 0D202020 37343830 33303839 [0344 .08479803]
0DCAA0190 20203434 45500320 30300252 31323003 [44 .PER.00.021]
0DCAA01A0 71893406 080900E5 36313032 31333130 [.4.q....20160131]
......
Argument/Register addr=0x00000000179BCA9B.
Dump of memory from 0x00000000179BCA5B to 0x00000000179BCB9B
0179BCA50 3720DAB5 3AD0C120 [.. 7 ..:]
0179BCA60 4C500A20 30302D53 3A363033 D3F7B520 [ .PLS-00306: ...]
0179BCA70 532720C3 454D5F50 41484352 545F544E [. 'SP_MERCHANT_T]
0179BCA80 534E4152 B1CA2027 FDCACEB2 FDCAF6B8 [RANS' ..........]
0179BCA90 E0C0F2BB EDB4CDD0 4F0AF3CE 302D4152 [...........ORA-0]
0179BCAA0 30353536 DAB5203A D0203120 B5202CD0 [6550: .. 1 .., .]
0179BCAB0 203720DA 203AD0C1 2F4C500A 3A4C5153 [. 7 ..: .PL/SQL:]
0179BCAC0 61745320 656D6574 6920746E 726F6E67 [ Statement ignor]
0179BCAD0 000A6465 0000000A FFF7A000 000007FF [ed..............]
0179BCAE0 00000000 00000000 00000000 00000000 [................]
0179BCAF0 000000AF 00000000 00000000 00000000 [................]
0179BCB00 7770D790 00000000 00000000 00000000 [..pw............]
0179BCB10 00000000 00000000 0C100000 00000000 [................]
0179BCB20 00000030 00000000 0C101438 00000000 [0.......8.......]
0179BCB30 00000000 00000000 77639685 00000000 [..........cw....]
0179BCB40 0C100000 00000000 00010002 00000000 [................]
0179BCB50 00000030 00000000 00000040 00000000 [0.......@.......]
0179BCB60 00000000 00000000 04D9874C 00000000 [........L.......]
0179BCB70 27AD1280 00000000 04D98BB0 00000000 [...'............]
0179BCB80 04D98940 00000000 04D9874C 00000000 [@.......L.......]
0179BCB90 04D98940 00000000 04D9874C [@.......L...]
Argument/Register addr=0x0000000014681F08.
从上面的信息看到是在执行一个 GROUP BY 分组操作的SQL使用 HASH GROP BY 导致 ORA-00600 无法完成数据聚合的错误。
在 metalink 使用 ORA-00600 [32695], [hash aggregation can't be done] 搜索到文档 ORA-600 [32695] [hash aggregation can't be done] (文档 ID 729447.1)
3、建议
1）在数据库系统集禁用 HASH GROUP BY，并刷新共享池
alter system set "_gby_hash_aggregation_enabled" = false scope=spfile;2
2）在会话级禁用 HASH GROUP BY ，并刷新共享池
alter session set "_gby_hash_aggregation_enabled" = false;
3）使用提示禁用 HASH GROUP BY
SQL> select /*+ NO_USE_HASH_AGGREGATION */ ...
4）安装补丁 patch:6471770修复该问题。
结论：通过TRACE 文件分析定位问题原因是执行一个 GROUP BY 分组操作的SQL导致 ORA-00600 无法完成数据聚合的错误。建议禁用 HASH GROUP BY 或安装装补丁 patch:6471770修复该问题。