Re: Sort question

"srivenu" <srivenu_at_hotmail.com> wrote in message news:1a68177.0306092343.5f074cd3_at_posting.google.com...
> I have a question on sorting and i request someone to explain the
> following the me.
> I have an Oracle instance running with a SORT_AREA_SIZE &
> SORT_AREA_RETAINED_SIZE of 5MB each.
> Still i saw that the application users were doing some sorting (I have
> plenty of RAM to burn).
> So I increased the sort sizes
> ALTER SYSTEM SET SORT_AREA_SIZE=26214400 DEFERRED;
> ALTER SYSTEM SET SORT_AREA_RETAINED_SIZE=26214400 DEFERRED;
>

--snip--
> SID Command Waiting For P1 P2
> P3 STATE Time
> ---- ------- ------------------------------ ----------- -----------
> ---------- ---------- ----
> 52 Select SQL*Net more data to client 1413697536 2035
> 0 WAITED SHO -1
> 24 Select direct path read 202 73838
> 114 WAITING 0
>
> As can be seen, session 24 is doing a read from the temporary segment.
> Why isnt this sort getting added as a disk sort in system statistics?
> I'am pretty sure that the sort area size of 25 MB i gave is sufficient
> for doing the sorting.
> Can some one explain me all this ?
> Thanks in advance for any replies.

Srivenu,

Try querying the v$sort_usage view to see what kind of activity is happening on the sort_segments. It might just be a hash join activity you are seeing ... or LOB activity. temp tablespace is not only used for sorting ... but for many other things.

.. so v$sort_usage.segtype

Anurag Received on Tue Jun 10 2003 - 20:19:15 CDT