(no subject)

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

• Original Message -----
  > Subject: _gby_hash_aggregation_enabled=false
  > Date: Thu, 29 Nov 2007 15:10:03 -0700
  > From: "Allen, Brandon" <Brandon.Allen_at_OneNeck.com>
  >
  > 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;
  >
  

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




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




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