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