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

一、这个参数的发展史：

_gby_hash_aggregation_enabled

---

Oracle 11.1.0:

Parameter Name:_gby_hash_aggregation_enabledDescription:enable group-by and aggregation using hash schemeType:BOOLObsoleted:FALSECan ALTER SESSION:TRUECan ALTER SYSTEM:IMMEDIATE

Oracle 10.2.0:

Parameter Name:_gby_hash_aggregation_enabledDescription:enable group-by and aggregation using hash schemeType:BOOLObsoleted:FALSECan ALTER SESSION:TRUECan ALTER SYSTEM:IMMEDIATE

Oracle 10.1.0:

No such parmeter in Oracle 10.1.0.

Oracle 9.2.0:

No such parmeter in Oracle 9.2.0.

Oracle 8.1.7:

No such parmeter in Oracle 8.1.7.

Oracle 8.0.6:

No such parmeter in 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改成了hash group by，这种算法上的改进，取消了sort group by必须进行的排序操作，即然是用hash算法，就存在碰撞的可能性，itpub的godlessme就碰到这样的问题，应该算是bug吧。

    下面给大家演示一下如何解决这种问题，其实要解决hash group by引起的排序不准确的问题，就是还用以前的sort group by就可以啦，10gR2中引入_gby_hash_aggregation_enabled隐藏参数，该参数默认设置为true，将它改成false即可。

    SQL> select status,count(*) from tmp_object group by status;
    STATUS COUNT(*)
    ---- -----
    INVALID 29
    VALID 10236

    Execution Plan
    -----------------------------
    Plan hash value: 3490974944
    -------------------------------------
    | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
    -------------------------------------
    | 0 | SELECT STATEMENT | | 2 | 12 | 35 (6)| 00:00:01 |
    | 1 | HASH GROUP BY | | 2 | 12 | 35 (6)| 00:00:01 |
    | 2 | TABLE ACCESS FULL| TMP_OBJECT | 10265 | 61590 | 33 (0)| 00:00:01 |
    -------------------------------------
    Statistics
    -----------------------------
    24 recursive calls
    0 db block gets
    136 consistent gets
    0 physical reads
    0 redo size
    522 bytes sent via SQL*Net to client
    385 bytes received via SQL*Net from client
    2 SQL*Net roundtrips to/from client
    0 sorts (memory)
    0 sorts (disk)
    2 rows processed

    SQL> col ksppinm format a39
    SQL> col ksppstvl format a39
    SQL> select ksppinm, ksppstvl 
    2 from x$ksppi pi, x$ksppcv cv 
    3 where cv.indx=pi.indx and pi.ksppinm like '_%' escape ''
    4 and pi.ksppinm like '%&parameter%';
    Enter value for parameter: gby
    old 4: and pi.ksppinm like '%&parameter%'
    new 4: and pi.ksppinm like '%gby%'

    KSPPINM KSPPSTVL
    -------------------- ------------
    _gby_onekey_enabled TRUE
    _gby_hash_aggregation_enabled TRUE

    SQL> alter session set "_gby_hash_aggregation_enabled"=false;
    Session altered.
    SQL> select status,count(*) from tmp_object group by status;
    STATUS COUNT(*)
    ---- -----
    INVALID 29
    VALID 10312

    Execution Plan
    -----------------------------
    Plan hash value: 1360369603
    -------------------------------------
    | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
    -------------------------------------
    | 0 | SELECT STATEMENT | | 10860 | 54300 | 32 (7)| 00:00:01 |
    | 1 | SORT GROUP BY | | 10860 | 54300 | 32 (7)| 00:00:01 |
    | 2 | TABLE ACCESS FULL| TMP_OBJECT | 10860 | 54300 | 30 (0)| 00:00:01 |
    -------------------------------------
    Statistics
    -----------------------------
    0 recursive calls
    0 db block gets
    134 consistent gets
    0 physical reads
    0 redo size
    522 bytes sent via SQL*Net to client
    385 bytes received via SQL*Net from client
    2 SQL*Net roundtrips to/from client
    0 sorts (memory)
    0 sorts (disk)
    2 rows processed

转载地址：http://tech.it168.com/db/o/2006-11-12/200611122129197.shtml

       其实在我看来，默认情况下Oracle会按数据块读取表中的数据，而我们在存入数据时有部分可能会按递增或递减的顺序在数据块中排列。当我们从数据块中读取数据时其实应该是有规律的顺序，或者说SORT group by 的排序就会满足我们的需求，而且对于小表来讲，这样的读取不会对性能有很大的影响。但是，从10g以后默认读取方式就变成了hash group by，导致本来可以按顺序读取的数据，还要加上order by 在内存中排序。而且，在对大表进行读取时，有可能报出

ORA-00600: internal error code, arguments: [32695], [hash aggregation can't be done], [], [], [], [], [], []的错误。

对于这个错误，我们只能讲参数_gby_hash_aggregation_enabled的默认值改为false。

附录：如何查看隐含参数：

SQL> SELECT x.ksppinm NAME, y.ksppstvlVALUE, x.ksppdesc describ

    FROM SYS.x$ksppi x, SYS.x$ksppcv y

  WHEREx.inst_id = USERENV ('Instance')

   AND y.inst_id = USERENV ('Instance')

   AND x.indx = y.indx

   AND x.ksppinm LIKE '%xxx%';