ORA-00600: 内部错误代码, 参数: [32695], [hash aggregation can't be done]

今天查看周报过程时 发现了它：

ORA-00600: 内部错误代码, 参数: [32695], [hash aggregation can't be done], [], [], [], [], [], []

其过程

P_PROD_WEEK_I_NEWANDLOST_USER 新增用户和流失分析。

以前它用很大的TEMP，32G才满足它的需求。 

ORACLE 版本： 10.2.03

 

Google：

 http://space6212.itpub.net/post/12157/399059

 

 It say:

  oracle的优化器使用了hash group by来进行数据分组。
hash group by是10gR2新引入的一个优化方式，它使group by时使用hash的方式进行分组，避免了排序操作。
在执行这个sql前我考虑到用大量用到temp表空间，而TEMP_CITI表非常大，因此我分配了足够大的临时表空间（至少是2倍TEMP_CITI表的大小），本以为不会出错，结果还是出错了。
上metalink查看一下，发现这是一个bug：
Applies to:
Oracle Server - Enterprise Edition - Version: 10.2.0.1 to 10.2.0.2
This problem can occur on any platform.
Symptoms
1). The following errors are encountered:

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

2). The error is occurring on a SELECT statement with a GROUP BY clause.
3). The call stack may resemble:

ksfdmp kgeriv kgesiv ksesic1 qeshPartitionBuildHD qeshGBYOpenScan2 qeshGBYOpenScan qerghFetch qertqoFetch qerpxSlaveFetch qerpxFetch insdlexe insExecStmtExecIniEngine insexe

Cause
The problem here is not the hash join, but the group by hash. Hash aggregation is new to 10.2. The GROUP BY hash clause can cause the statement to consume temporary tablespace resources and eventually fail with the error ORA-00600: internal error code, arguments: [32695], [hash aggregation can't be done].

安全吻合metalink的描述。metalink上也给出了两种解决方法：

1). set _gby_hash_aggregation_enabled = false e.g.:

alter system set "_gby_hash_aggregation_enabled"=false;
alter session set "_gby_hash_aggregation_enabled"=false;

2). Disable the use of hash group by changing the parameter "optimizer_features_enable":

set optimizer_features_enable to "10.1.0"