RE: 10g ORDER BY Performance (not sure about that subject line)

  • From: "Mark W. Farnham" <mwf@xxxxxxxx>
  • To: "'Oracle-l'" <oracle-l@xxxxxxxxxxxxx>
  • Date: Tue, 28 Aug 2007 11:57:37 -0400

First, I'm a bit surprised that disabling hash aggregation didn't force sort
aggregation which should produce an ordered set of groups as a side effect
(not to be confused with valid SQL and specifically warned against being
relied on by Oracle since at least November 1988.) I would check the plan
generated and v$parameter to be certain the setting is being effectively set
simultaneously with getting an unordered result set.

Did I get it correct from the original post that these all involve group by

If verifying the plan and parameter setting yields no joy, then it seems
likely that a sed, awk or perl script to pass across your script library and
generate a ORDER BY that matches the GROUP BY is not too much of a stretch.
Since you have over 1000 scripts it seems likely this is less error prone
than attempting hand repairs.

-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Peter Barnett
Sent: Friday, August 24, 2007 1:49 PM
To: Oracle-l
Subject: Re: 10g ORDER BY Performance

We already have that set for another problem and still
have the unordered sort issue.  

This is really a coding issue but we dno't have time
to fix it.

--- Ghassan Salem <salem.ghassan@xxxxxxxxx> wrote:

> It's not the new sort that doing this, it's the
> 'hash group by'
> try disabling it using
> alter ... set "_gby_hash_aggregation_enabled"=false;
> On 8/24/07, goran bogdanovic <goran00@xxxxxxxxx>
> wrote:
> >
> >
> >
> > you may set it for a session too:
> >
> > alter session set "_newsort_enabled"=false;
> >


Other related posts:

  • » RE: 10g ORDER BY Performance (not sure about that subject line)