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

Do you have NLS_SORT set? If it is different from BINARY you are asking for an explicit sort instead of exploiting an already existing index order.

Please check in your session with

select * from v$nls_parameters;

Martin

Darryl wrote:
>
> 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
Received on Fri Mar 23 2001 - 18:10:32 CST