Re: Order By Bogs down the Statement

From: "Rich McGillicuddy" >>
These queries run real fast except if I add an order by to the SQL Statement. Why is this and how can I speed it up. <<

When you run a query without an order by clause Oracle can start spooling the results back to you as soon as Oracle determines that a row meets all selection requirements. When you add an Order by clause Oracle can not send any results to the calling application or screen until it has located all rows and sorted them.

So, 1) Make sure you are timing the actual completion of the query and not just the start of spooling of the results
then,
 2) look at your init.ora parameter setting for the amount of memory Oracle allocates to user processes for sorting: sort_area_size and sort_area_retained_size. You should have at least 64K. You do not need a huge retained sort area since every session reserves this space weather it sorts or not, and does not release it. What you want is for 99+% of all sorts to take place in memory. Large sorts are going to have to go to disk on most systems so...
3) If you do not have a seperate sort tablespace create one 4) Check that the initial and next extent are set equal with a pctincrease of 0 and that they are set to a decent size, 512k or larger. Later you can research declaring the tablespace temporary.

I hope these suggestions help, and since these queries are running under a personal Oracle you can probably use a meg for sort_area, but if the queries will be moved into a production instance you may not want to do that.

Mark Powell -- Oracle 7 Certified DBA
- The only advice that counts is the advice that you follow so follow your own advice - Received on Thu Nov 05 1998 - 19:24:14 CST