RE: 10g ORDER BY Performance

Hi Pete,

wow! haven't seen such mess before!

If the scripts have been tested on 9i then go live with 9i (although you will definitely have some surprises, when someone enables parallelism, oracle starts favouring range scan on another index etc etc etc etc).

I would freeze any changes to db schema, optimizer & system stats close to absolute zero.

otherwise if 10g is inevitable, set the optimizer_features_enabled to 9i and freeze all stats again.

anyway, if you're the one who has to support this application I think it's wise to start looking for a new job ;)

good luck!

--
Regards,
Tanel Poder
http://blog.tanelpoder.com
 



> -----Original Message-----

> From: oracle-l-bounce_at_freelists.org 

> [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Peter Barnett

> Sent: Friday, August 24, 2007 21:48

> To: Oracle-l

> Subject: 10g ORDER BY Performance

> 

> 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