Re: 10g ORDER BY Performance

do all these queries have group by ?

Oracle has introduced a new grouping algorithm visible in explain plan as HASH GROUP BY instead of SORT GROUP BY which can change the ordering in aggregate queries.

If thats the case then you can try disabling this new algorithm by

setting parameter
_gby_hash_aggregation_enabled to false

amit

Peter Barnett wrote:
> First, the disclaimer, someone made a very bad coding
> decision! Now the question is how to get out of the
> mess.
>
> We have about 2,000 scripts that do not have ORDER BY
> clauses anywhere in them. In 9i all of the queries
> returned the rows in the correct order. More a lucky
> accident than anything else.
>
> In 10g the optimizer has changed and the results are
> returned unordered. Since we are weeks away from go
> live of a major project there is no time to edit and
> test all of the scripts. Management is now wanting us
> to go live on 9i rather than 10g. Something the DBA
> team is really resisting but may be necessary.
>
> I came across a _newsort_enabled parameter which did
> not change the result set in 10g. Is anyone aware of
> a parameter that can be set in 10g that will give the
> 9i behavior other than setting the
> optimizer_features_enabled to 9.2? If we do that we
> might as well be on 9i.
>
>
> Pete Barnett
>
>
>
> ____________________________________________________________________________________
> Choose the right car based on your needs. Check out Yahoo! Autos new Car Finder tool.
> http://autos.yahoo.com/carfinder/
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>
>

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