今天，在測試一個過程的是遇到一個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。

hash group by導致ORA-600

相關文章