RE: _gby_hash_aggregation_enabledúlse
- From: "Taylor, Chris David" <Chris.Taylor@xxxxxxxxxxxxxxx>
- To: <jonathan@xxxxxxxxxxxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 30 Nov 2007 12:05:28 -0600
Jonathan,
Something I pointed out to Brandon in a separate email, is that the
behavior doesn't seem to reverse when you set that parameter back to
true.
Queries continue to use SORT (GROUP BY)...
I would be curious if you see the same thing...
Chris Taylor
Sr. Oracle DBA
Ingram Barge Company
Nashville, TN 37205
Office: 615-517-3355
Cell: 615-354-4799
Email: chris.taylor@xxxxxxxxxxxxxxx
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Jonathan Lewis
Sent: Friday, November 30, 2007 11:36 AM
To: oracle-l@xxxxxxxxxxxxx
Subject: Re: _gby_hash_aggregation_enabled=false
I've just tried the following on 10.2.0.1
select owner, count(*) from dba_objects
group by owner;
Autotrace produced a plan with a hash group by.
Then I did
alter session set "_gby_hash_aggregation_enabled" = false;
and repeated the query - the plan switched to a sort group by
(Your sample query from v$sql_plan defaulted to a sort
group by anyway, which is why I had to find another query).
A possible explanation for your observation - if I ran the test
using SQL_TRACE rather than autotrace, the first cursor
was not invalidated when I changed the setting for the
parameter. Try repeating your test with a comment in
the text to make the before and after versions of the query
look different.
You might also try the /*+ no_use_hash_aggregation */ hint.
Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
Author: Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html
The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html
----- Original Message -----
> Subject: _gby_hash_aggregation_enabled=false
> Date: Thu, 29 Nov 2007 15:10:03 -0700
> From: "Allen, Brandon" <Brandon.Allen@xxxxxxxxxxx>
>
> 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;
>
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
