hash group by导致ORA-600

今天，在测试一个过程的是遇到一个ORA-00600错误：
Errors in file /opt/oracle/admin/citizen/bdump/citizen_j000_25812.trc:
ORA-00600: internal error code, arguments: [32695], [hash aggregation can't be done], [], [], [], [], [], []
以下是从trace文件摘取出来的一些主要信息：
ksedmp: internal or fatal error
ORA-00600: internal error code, arguments: [32695], [hash aggregation can't be done], [], [], [], [], [], []
Current SQL statement for this session:
INSERT /*+ append PARALLEL(a,2) */ INTO CITIZENINFO A SELECT :B1 , DBMS_UTILITY.GET_HASH_VALUE(REALNAME, 1, 10000001), DBMS_UTILITY.GET_HASH_VALUE(REALNAME, 1, 2000000001), COUNT(1) FROM TEMP_CITI GROUP BY DBMS_UTILITY.GET_HASH_VALUE(REALNAME, 1, 10000001), DBMS_UTILITY.GET_HASH_VALUE(REALNAME, 1, 2000000001);


数据库版本信息：
SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bi
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for Linux: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

先看看这个SQL的执行计划：
SQL> var b1 varchar2(2)
SQL> exec :b1:='1'

PL/SQL procedure successfully completed.

SQL> set autotrace trace exp
SQL> SELECT :B1,
2 DBMS_UTILITY.GET_HASH_VALUE(REALNAME, 1, 10000001),
3 DBMS_UTILITY.GET_HASH_VALUE(REALNAME, 1, 2000000001),
4 COUNT(1)
5 FROM TEMP_CITI
6 GROUP BY DBMS_UTILITY.GET_HASH_VALUE(REALNAME, 1, 10000001),
7 DBMS_UTILITY.GET_HASH_VALUE(REALNAME, 1, 2000000001);

Execution Plan
----------------------------------------------------------
Plan hash value: 687460396

--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 52 | 3 (34)| 00:00:01 |
| 1 | HASH GROUP BY | | 1 | 52 | 3 (34)| 00:00:01 |
| 2 | TABLE ACCESS FULL| TEMP_CITI | 1 | 52 | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------------
从执行计划可以看到，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"

其实两种解决方法本质是一样的：就是禁用hash group by。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/231499/viewspace-63856/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/231499/viewspace-63856/