Re: SQL/DB Tuning Question(Order by)?

Use the EXPLAIN PLAN, or AUTOTRACE facility to find out what the two different access paths are.

It possible that Oracle has decided that it can do the ORDER BY without sorting by
using an indexed access path starting on a different table. This (no sort) path then ends up doing far more logical I/O than the original.

--
Jonathan Lewis
Yet another Oracle-related web site:  http://www.jlcomp.demon.co.uk

Practical Oracle 8i:  Building Efficient Databases
Publishers:  Addison-Wesley

Reviews at: http://www.jlcomp.demon.co.uk/book_rev.html



Darryl wrote in message <3AB1C8CA.D269F652_at_ix.netcom.com>...


>I have this query that that joins about 5 tables.  Without sorting the

>results with Order by.  It returns in about 3 seconds.  When I add a

>simple order by clause the query takes about 40 seconds. My first

>thought was that it was sorting using Disk.  But autotrace indicated

>that all of the sorting was taking place in memory.  I even bumped up

>SORT_AREA_SIZE to 5 meg just in case.  I also noticed that one of the

>CPU's on the box(it has 6 CPU's) had a server process that was taking up

>a 100% of the CPU time.  The result set is only about 200 rows.  Any

>Ideas on whats going on?  Is there any way to spread this sorting

>activity over multiple CPU's?  None of the documentation of tuning

>sorting seems to cover this situation.  Is there any way to speed up

>sorting other that making sure that it happens in memory?  Running 8.1.7

>on HU-UX 11.

>

>TIA

>