_gby_hash_aggregation_enabledバグ

カスタムーからの問い合わせ：オラクルがどんな状況でもHASH GROUP BYを選択しないが、理由は何?

オラクルバージョンは10gR2（10.2.0.1）であります。

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

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

次のクエリを見れば、一番簡単なGroup By文でもHASH GROUP BYではなくてSORT 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 |
-------------------------------------------

隠しパラメーターである_gby_hash_aggregation_enabledの値がFalseである可能性があるでしょう。確かそうです。

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

しかし、_gby_hash_aggregation_enabledパラメーターをTrueに変え、USE_HASH_AGGREGATIONヒントを与えても、かわりなくSORT GROUP BYが選べられます。

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 |
-------------------------------------------

これは正常的な状況とは言えません。MOSで検索してみたら、完璧に一致するバグが見つかりました。

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.

このバグはパラメーターファイルで_gby_hash_aggregation_enabledパラメーターの値を指定する時にのみ起こります。

*._gby_hash_aggregation_enabled=FALSE

初期のHASH GROUP BY機能が多くのバグを持っていたから、パラメーターファイルでこのパラメーターをFalseで指定しているシステムが多いと思います。でもUSE_HASH_AGGREGATIONヒントを使うにもかかわらずHASH GROUP BY機能が動作しないというのは予想しなかった副作用でしょう。

このバグは10.2.0.5でパッチされました。