_gby_hash_aggregation_enabled bug

One of the customers complained that Oracle refuses to use the HASH GROUP BY operation under any circumstances.

It’s 10gR2(10.2.0.1) database.

TPACK@ukja1021> select * from v$version where rownum = 1;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod

See that Oracle simply refuses to use HASH GROUP BY operation even with the simplest group by?

create table t1
as
select level as c1
from dual
connect by level <= 10;


explain plan for 
select count(*)
from tpack.t1
group by c1;

-------------------------------------------
| Id  | Operation          | Name | Rows  |
-------------------------------------------
|   0 | SELECT STATEMENT   |      |    10 |
|   1 |  SORT GROUP BY     |      |    10 |
|   2 |   TABLE ACCESS FULL| T1   |    10 |
-------------------------------------------

You might think that the “_gby_hash_aggregation_enabled” would have been false which is clearly right.

TPACK@ukja1021> col value format a10
TPACK@ukja1021> @para gby_hash
TPACK@ukja1021> set echo off
old   9: and i.ksppinm like '%&1%'
new   9: and i.ksppinm like '%gby_hash%'

NAME                           VALUE                IS_DEFAUL SES_MODIFI
------------------------------ -------------------- --------- ----------
SYS_MODIFI
----------
DESCRIPTION
-------------------------------------------------------------------------

_gby_hash_aggregation_enabled  FALSE                FALSE     true
immediate
enable group-by and aggregation using hash scheme

But even with the parameter enabled and USE_HASH_AGGREGATION hint applied, Oracle still persists to the SORT GROUP BY operation.

alter session set "_gby_hash_aggregation_enabled" = true;

explain plan for 
select /*+ use_hash_aggregation */ count(*)
from t1
group by c1;

-------------------------------------------
| Id  | Operation          | Name | Rows  |
-------------------------------------------
|   0 | SELECT STATEMENT   |      |    10 |
|   1 |  SORT GROUP BY     |      |    10 |
|   2 |   TABLE ACCESS FULL| T1   |    10 |
-------------------------------------------

Okay, enough. This is not a normal situation. The last resort is MOS. Following bug seems to perfectly match this case.

Bug 8631742: ALTER SESSION SET ... NOT HAVING EFFECT IN 10.2.0.4
...
RELEASE NOTES:
]] Setting _gby_hash_aggregation_enabled at the session level did not
]] always take effect
REDISCOVERY INFORMATION:
If you change the use of hash aggregation at the session level, but this does
not affect the choice of aggregation method in subsequebtly parsed SQL,
you are probably hitting this bug.

One thing to note is that you might see this bug only when you specify the parameter in the parameter file which would be a a bit rare case.

*._gby_hash_aggregation_enabled=FALSE

I know that the earlier implementation of the HASH GROUP BY operation has some serious bugs which made many sites simply turn off it permanently. The problem is that you can’t use that operation even with USE_HASH_AGGREGATION hint which you wouldn’t have expected. :)

FIY, this bug was patched at 10.2.0.5.