Dion Cho – Oracle Performance Storyteller

We are natural born scientists

_gby_hash_aggregation_enabled bug

with 2 comments

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

It’s 10gR2( database.

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

Oracle Database 10g Enterprise Edition Release - Prod

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

create table t1
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
------------------------------ -------------------- --------- ----------

_gby_hash_aggregation_enabled  FALSE                FALSE     true
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.

]] Setting _gby_hash_aggregation_enabled at the session level did not
]] always take effect
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.


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

Written by Dion Cho

July 6, 2010 at 5:52 am

2 Responses

Subscribe to comments with RSS.

  1. Hi Dion,

    This is also known in PeopleSoft.. together with the _UNNEST_SUBQUERY

    See here http://www.freelists.org/post/oracle-l/PeopleSoft-query,7


    July 6, 2010 at 7:55 am

    • Hi, Karl.

      Thanks for the link. I now know that this is an well-known issue. :)

      Dion Cho

      July 6, 2010 at 8:03 am

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: