Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE:__gby_hash_aggregation_enabled

RE:__gby_hash_aggregation_enabled

From: Taylor, Chris David <Chris.Taylor_at_ingrambarge.com>
Date: Fri, 30 Nov 2007 12:35:08 -0600
Message-ID: <17E4CDE8F84DC44A992E8C00767402E0860D56@spobmexc02.adprod.directory>

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_at_ingrambarge.com

-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Taylor, Chris David Sent: Friday, November 30, 2007 12:18 PM To: Taylor, Chris David; jonathan_at_jlcomp.demon.co.uk; oracle-l_at_freelists.org 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_at_ingrambarge.com

-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Taylor, Chris David Sent: Friday, November 30, 2007 12:05 PM To: jonathan_at_jlcomp.demon.co.uk; oracle-l_at_freelists.org 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_at_ingrambarge.com

-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Jonathan Lewis Sent: Friday, November 30, 2007 11:36 AM To: oracle-l_at_freelists.org
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

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




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




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




--
http://www.freelists.org/webpage/oracle-l
Received on Fri Nov 30 2007 - 12:35:08 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US