ORA-00600:[32695], [hash aggregation can't be done] 解决方法

一．问题描述

数据库报错，看了一下alertlog，主要重复如下内容：

Wed Jul 25 17:47:18 2012

Errors in file /oracle/admin/etldb/udump/etldb_ora_15674.trc:

ORA-07445: exception encountered: core dump [kghssgdmp()+273] [SIGFPE] [Integerdivide by zero] [0x40000000094555E1] [] []

ORA-07445: exception encountered: core dump[kghssgdmp()+273] [SIGFPE] [Integer divide by zero] [0x40000000094555E1] [] []

ORA-00600: internal error code, arguments:[32695], [hash aggregation can't be done], [], [], [], [], [], []

--数据库版本：

SQL> select * from v$version;

BANNER

----------------------------------------------------------------

Oracle Database 10g Enterprise EditionRelease 10.2.0.4.0 - 64bi

PL/SQL Release 10.2.0.4.0 - Production

CORE 10.2.0.4.0 Production

TNS for HPUX: Version 10.2.0.4.0 -Production

NLSRTL Version 10.2.0.4.0 - Production

--查看/oracle/admin/etldb/udump/etldb_ora_15674.trc文件：

ORA-00600: internal error code, arguments:[32695], [hash aggregation can't be done], [], [], [], [], [], []

Current SQL statement for this session:

INSERT /*+ APPEND */ INTO DMID.TMP_MD_PAR_CON_4_M7 NOLOGGING

(

…

)

SELECT

…

FROM DMID.TMP_MD_PAR_CON_4_M8 T2

GROUP BY T2.DEAL_DATE,T2.BILLCYCL_ID

,T2.SCATT_ACCT

,T2.CONTRACT_NO

----- PL/SQL Call Stack -----

object line object

handle number name

c0000003ce3b5618 1062 procedure DMID.P_MD_PAR_CON_4_M

c000000322524998 1 anonymous block

….

二．说明：

MOS 文档：

ORA-600 [32695] [hash aggregation can't bedone] [ID 729447.1]

2.1 Applies to:

Oracle Server -Enterprise Edition - Version: 10.2.0.1 to 11.1.0.6 - Release: 10.2 to 11.1 Informationin this document applies to any platform.
***Checked for relevance on 17-Nov-2011***

2.2 Symptoms

When running astatement that involves a GROUP BY operation, the following error is raised:

ORA-00600:internal error code, arguments: [32695], [hash aggregation can't be done], [],[],

If we look in the trace file, underCall Stack Trace section, we see the functions:

... qeshPartitionBuildHD qeshGBYOpenScan2qeshGBYOpenScan qerghFetch qervwFetch ...

and the query plan for the SQL statementshows a HASH GROUP BY, eg. :

------------------------------------------
| Id |Operation                        |
------------------------------------------
| 0 |INSERTSTATEMENT                  |
| 1 | PXCOORDINATOR                    |
| 2 | PXSEND QC(RANDOM)               |
| 3 |HASH GROUPBY                     |
...

A second case where this would occur couldbe with a failing query that has no GROUP BY, but has a Select Distinct. ThePlan table indicates a HASH UNIQUE instead of HASH GROUP BY.

2.3 Cause

This is likely to be a case of unpublishedbug 6471770 - see eg. note:6471770.8- fixed in 10.2.0.5, 11.1.0.7, and 11.2
----导致ORA-600[32685] 可能是bug6471770,其在10.2.0.5，11.1.0.7 和11.2 中已经修复。

A similar problem is reported in:
bug:5893340 ORA-600 [32695], [HASH AGGREGATION CAN'T BE DONE]
- fixed in 10.2.0.4, 11.1.0.6

--导致ORA-600[32685]也可能是bug:5893340，其在10.2.0.4和11.1.0.6中已经修复。