'sort output' long operation
619091May 28 2009 — edited Jun 1 2009Our database is recently upgraded from 9.2 to 10.2.0.4 (OS: Solaris 9) and of late we have been seeing performance issues with our daily jobs. The job does not stall but it takes a longer time to complete.
Both table and index statistics are upto date.
Major wait event is i/o (db file seq read)
V$session_longops shows 'index fast full scan' and 'sort output' as the longest running operation from our job. 'sort output' somtimes show more than 4 hrs.
Our pga is sized at 750M and hit ratio at 94%
A join query on v$sql and v$tempseg_usage shows one of the 'select' query from our job using 88M in temp tbs. The entry exist for a long time which makes me beleive the sort is actually happening but taking a long time to complete. However, I can see the 'sort output' operation moving from OEM.
My question is, what does 'sort output' long operation exactly mean ? If the elapsed time for that is too high what could be the cause and how to alleviate it ?