RE:__gby_hash_aggregation_enabled
- From: "Taylor, Chris David" <Chris.Taylor@xxxxxxxxxxxxxxx>
- To: "Taylor, Chris David" <Chris.Taylor@xxxxxxxxxxxxxxx>, <jonathan@xxxxxxxxxxxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 30 Nov 2007 12:35:08 -0600
Looks like OPTIMIZER_MODE affects this.
If OPTIMIZER_MODE=CHOOSE, that query uses a SORT (GROUP BY) plus other things.
If OPTIMIZER_MODE=ALL_ROWS, that query uses a HASH (GROUP BY)
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 Taylor, Chris David
Sent: Friday, November 30, 2007 12:18 PM
To: Taylor, Chris David; jonathan@xxxxxxxxxxxxxxxxxx; oracle-l@xxxxxxxxxxxxx
Subject: =?iso-8859-1?Q?RE:__gby_hash_aggregation_enabledúlse?Date: Fri, 30 Nov
2007 12:09:10 -0600
Ok, now that's really weird.
In one of my DBs it does in fact reverse, but in our main Psoft database it
does not.
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 Taylor, Chris David
Sent: Friday, November 30, 2007 12:05 PM
To: jonathan@xxxxxxxxxxxxxxxxxx; oracle-l@xxxxxxxxxxxxx
Subject: RE: _gby_hash_aggregation_enabledúlse
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
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
- » RE:__gby_hash_aggregation_enabled
