ORA-00600: [32695], [hash aggregation can't be done] Solution

 

I. Problem Description

Database Error. Read alertlog, which repeats the following content:

 

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], [], [], [], [], [], [], [], []

 

 

-- Database version:

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

 

 

-- View the/Oracle/admin/etldb/udump/etldb_ora_15674.trc file:

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

....

 

 

Ii. Description:

 

Mos document:

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

 

 

2.1 applies:

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 wowould 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.

 

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
---- Cause the ORA-600 [32685] may be bug6471770, which has been fixed in 10.2.0.5, 11.1.0.7, and 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

-- Cause the ORA-600 [32685] may also be bug: 5893340, which has been fixed in 10.2.0.4 and 11.1.0.6.

 

2.4 Solution

Solutions are as follows:

You can use the following methods to solve the problem without upgrading the database:

 

1) Disable hash group by operations bysetting the parameter _ gby_hash_aggregation_enabled to false, ie .:

SQL> alter session set "_ gby_hash_aggregation_enabled" = false;

Or

SQL> alter systemset "_ gby_hash_aggregation_enabled" = falsescope = spfile;

 

A hard parse tothe statement needs to be shortmed, preferably to flush the Shared Pool aftersetting this workaround and then re-run the statement.

-- To make the modification take effect, you need to execute a hard resolution, so you can select flush share pool and then execute SQL.

 

"_ Gby_hash_aggregation_enabled" is an implicit parameter of Oracle. We can use the all_parameters view to query.

 

SQL> select name, value fromall_parameters where name like '_ gby_hash_aggregation _ % ';

 

NAME value

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

_ Gby_hash_aggregation_enabled true

 

Oracleall_parameters View

Http://blog.csdn.net/tianlesoftware/article/details/6641281

 

2) Disable hash group by operations byusing the hint no_use_hash_aggregation:

-- Use hint to disable the hashgroup by operation:

SQL> select/* + no_use_hash_aggregation */...

 

Oracle hint

Http://blog.csdn.net/tianlesoftware/article/details/5833020

 

Common oraclehint usage

Http://blog.csdn.net/tianlesoftware/article/details/4969702

 

3) apply patch: 6471770 if available forthe relevant platform/version

-- Application patch:

 

 

For Windows, the patch is already ded in:
10.2.0.3 patch 23 and later-See note: 342443.1
10.2.0.4 patch 5 and later-See note: 342443.1

Please note that the patch is crucial toresolve the second case of the symptoms section above, the workarounds will notresolve the issue.

If the error still reproduces followingthese steps, contact the Oracle Support.

 

 

 

 

 

 

 

Bytes -------------------------------------------------------------------------------------------------------

All rights reserved. reprinted articles are allowed, but source addresses must be indicated by links. Otherwise, the documents will be held legally responsible!

Skype: tianlesoftware

QQ: tianlesoftware@gmail.com

Email: tianlesoftware@gmail.com

Blog: http://www.tianlesoftware.com

WEAVER: http://weibo.com/tianlesoftware

Twitter: http://twitter.com/tianlesoftware

Facebook: http://www.facebook.com/tianlesoftware

LinkedIn: http://cn.linkedin.com/in/tianlesoftware

 

 

------- Add a group to describe the relationship between Oracle tablespace and data files in the remarks section. Otherwise, reject the application ----

Dba1 group: 62697716 (full); dba2 group: 62697977 (full) dba3 group: 62697850 (full)

Super DBA group: 63306533 (full); dba4 group: 83829929 dba5 group: 142216823

Dba6 group: 158654907 dba7 group: 172855474 DBA group: 104207940