Re: _gby_hash_aggregation_enabled=false

Brandon

I don't remember the name of the hint off hands, but AFAIK there is one that lets you set the optimizer features enabled per statement.

Stefan

On Nov 29, 2007 11:10 PM, Allen, Brandon <Brandon.Allen_at_oneneck.com> wrote:

> 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.3instead.
>
>
>
> 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.
>

-- 
=========================

Stefan P Knecht
Consultant
Infrastructure Managed Services

Trivadis AG
Europa-Strasse 5
CH-8152 Glattbrugg

Phone +41-44-808 70 20
Fax +41-808 70 12
Mobile +41-79-571 36 27
stefan.knecht_at_trivadis.com
http://www.trivadis.com

OCP 9i/10g SCSA SCNA
=========================

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