本文共 4470 字,大约阅读时间需要 14 分钟。
数据库报错,看了一下alertlog,主要重复如下内容:
Wed Jul 25 17:47:18 2012
Errors in file :
: exception encountered: core dump [kghssgdmp()+273] [SIGFPE] ] [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]
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***
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.
This is likely to be a case of unpublishedbug 6471770 - see eg. - 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: ORA-600 [32695], [HASH AGGREGATION CAN'T BE DONE] - fixed in 10.2.0.4, 11.1.0.6--导致ORA-600[32685]也可能是,其在10.2.0.4和11.1.0.6中已经修复。
Solutions are as follows:
在不升级DB的情况下,可以使用如下方法来解决:
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 performed, preferably to flush the Shared Pool aftersetting this workaround and then re-run the statement.
--要使修改生效,需要执行一次硬解析,所以可以选择flush share pool,然后执行SQL。
"_gby_hash_aggregation_enabled" 是Oracle的隐含参数,我们可以使用all_parameters 视图来查询。
SQL> select name,value fromall_parameters where name like '_gby_hash_aggregation_%';
NAME VALUE
--------------------------------------------------
_gby_hash_aggregation_enabled TRUE
Oracleall_parameters 视图
2) Disable HASH GROUP BY operations byusing the hint NO_USE_HASH_AGGREGATION:
--使用hint 禁用Hashgroup by 操作:
SQL> select /*+ NO_USE_HASH_AGGREGATION*/ ...
Oracle Hint
常见OracleHINT的用法
3) Apply if available forthe relevant platform/version
--应用Patch:
For Windows, the patch is included in:
10.2.0.3 patch 23 and later - see 10.2.0.4 patch 5 and later - seePlease 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.
-------------------------------------------------------------------------------------------------------
版权所有,文章允许转载,但必须以链接方式注明源地址,否则追究法律责任!
QQ:492913789
Email:ahdba@qq.com
Blog:
Weibo:
Twitter:
Facebook:
Linkedin:
-------加群需要在备注说明Oracle表空间和数据文件的关系,否则拒绝申请----
DBA1 群:62697716(满); DBA2 群:62697977(满) DBA3 群:62697850(满)
DBA 超级群:63306533(满); DBA4 群:83829929 DBA5群: 142216823
DBA6 群:158654907 DBA7 群:172855474 DBA总群:104207940