关于oracle读取数据时，自动进行HASH处理的隐含参数：_gby_hash_aggregation_enabled(一)

一、这个参数的发展史：

_gby_hash_aggregation_enabled

Oracle 11.1.0:

Oracle 10.2.0:

Oracle 10.1.0:

Oracle 9.2.0:

Oracle 8.1.7:

Oracle 8.0.6:

Oracle 7.3.4:

No such parmeter in Oracle 7.3.4.

二、关于ORACLE的两种group by 方式：hash group by 与 sort group by

Oracle10g在distinct操作时作了算法改进，使用Hash Unique 代理了以前的Sort Unique.该行为由隐藏参数”_gby_hash_aggregation_enabled”决定，optimizer_features_enable设置为10.2.0.1时默认为TRUE.

HASH UNIQUE 的CPU COST应该比SORT UNIQUE要低，同理常用HASH JOIN而少用SORT MERGE JOIN。

SQL> create table t as select * from dba_users;
Table created.

SQL> set autotrace on
SQL> select distinct password from t;
―――――――――――?
| Id | Operation | Name |
―――――――――――?
| 0 | SELECT STATEMENT | |
| 1 | SORT UNIQUE | |
| 2 | TABLE ACCESS FULL| T |
―――――――――――?
Note
―?
- rule based optimizer used (consider using cbo)
Statistics
―――――――――――――――――――-
1 recursive calls
0 db block gets
3 consistent gets
1 physical reads
0 redo size
752 bytes sent via SQL*Net to client
469 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
9 rows processed

RBO模式下，仍然要做SORT,使用的是 SORT UNIQUE

SQL> show parameters opt
NAME TYPE VALUE
―――――――――――― ―――? ――――――――――
optimizer_features_enable string 10.2.0.1
optimizer_mode string RULE

SQL> alter session set optimizer_mode = choose;
Session altered.

SQL> analyze table t compute statistics;
Table analyzed.

SQL> select distinct password from t;

Execution Plan
―――――――――――――――――――-
Plan hash value: 1901613472
―――――――――――――――――――――――――
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
―――――――――――――――――――――――――
| 0 | SELECT STATEMENT | | 9 | 144 | 3 (34)| 00:00:01 |
| 1 | HASH UNIQUE | | 9 | 144 | 3 (34)| 00:00:01 |
| 2 | TABLE ACCESS FULL| T | 9 | 144 | 2 (0)| 00:00:01 |
―――――――――――――――――――――――――
Statistics
―――――――――――――――――――-
1 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
752 bytes sent via SQL*Net to client
469 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
9 rows processed

HASH UNIQUE避免了排序，在数据量很大的时候应该能够看到较低的%CPU COST

SQL> ALTER SESSION SET “_gby_hash_aggregation_enabled” = FALSE;
SQL> select distinct password from t;
Execution Plan
―――――――――――――――――――-
Plan hash value: 965418380
―――――――――――――――――――――――――
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
―――――――――――――――――――――――――
| 0 | SELECT STATEMENT | | 9 | 144 | 3 (34)| 00:00:01 |
| 1 | SORT UNIQUE | | 9 | 144 | 3 (34)| 00:00:01 |
| 2 | TABLE ACCESS FULL| T | 9 | 144 | 2 (0)| 00:00:01 |
―――――――――――――――――――――――――
Statistics
―――――――――――――――――――-
1 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
752 bytes sent via SQL*Net to client
469 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
9 rows processed

三、hash group by的bug及解决办法（转载）

由于本人还没有遇到相关的bug,所以在这里就先引用前辈的经验。希望前辈不要介意。

在10gR2中，group by由以前的sort group by改成了h