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úlse

RE: _gby_hash_aggregation_enabledúlse

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


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
Received on Fri Nov 30 2007 - 12:05:28 CST

Original text of this message

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