Re: _gby_hash_aggregation_enabled=false

On a side note I've recently found another confirmation that hints are directives not just "hints" :) At least hint use_hash is "stronger" than parameters _hash_join_enabled = false (for 10g) or hash_join_enabled = false (for 9i). Example here http://gplivna.blogspot.com/2007/10/hints-stronger-than-db-parameters.html

However it seems it is not quite related to your case because AFAIK there isn't possibility to force one or another group by mechanism using hints and you don't change your query.

Gints Plivna
http://www.gplivna.eu

2007/11/30, Allen, Brandon <Brandon.Allen_at_oneneck.com>:
>
>
> Has anyone else tried setting this hidden parameter and found it didn't
> disable the HASH GROUP BY feature? It's suggested in Metalink 387958.1 as a
> workaround to the "wrong results" bug (4604970) with the new HASH GROUP BY
> feature, so I just set it as follows:
>
> SQL> alter system set
> "_gby_hash_aggregation_enabled"=false;
>
> System altered.
>
>
> Then I ran a 10046 trace on a query known to have the HASH GROUP BY
> operation in its explain plan:
>
> SQL> alter session set sql_trace=true;
>
> Session altered.
>
> SQL> @dts1
> . . .
> 2310 rows selected.
>
>
> But, in the trace file, it still shows that HASH GROUP BY is being used:
>
> STAT #1 id=2 cnt=952 pid=1 pos=1 obj=0 op='HASH GROUP BY (cr=2395 pr=0 pw=0
> time=49601 us)'
>
>
>
> Next, I tried setting optimizer_features_enable="10.1.0" as recommended in
> the same Metalink article and that worked as expected - the trace file
> showed SORT GROUP BY instead of the HASH GROUP BY.
>
> I've already got an SR open, so we'll see what they say but I'm curious if
> others have encountered the same thing. I don't want to set
> optimizer_features_enable to 10.1.0 at the system level so if the hidden
> parameter doesn't really work, then I'll have to install patchset 10.2.0.3
> instead.
>
>
>
> Thanks,
>
> Brandon
>
>
>
> Privileged/Confidential Information may be contained in this message or
> attachments hereto. Please advise immediately if you or your employer do not
> consent to Internet email for messages of this kind. Opinions, conclusions
> and other information in this message that do not relate to the official
> business of this company shall be understood as neither given nor endorsed
> by it.

--
http://www.freelists.org/webpage/oracle-l