Re: sorts (disk) and v$sort_usage

What happens if you rewrite the query as such:

select a.name,b.value
from v$statname a, v$sesstat b, v$session s, v$sort_usage su where a.statistic#=b.statistic# and b.sid=s.sid and s.saddr=su.session_addr
and a.name='sorts (disk)';

Also, did you check the segment type (SEGTYPE) in V$SORT_USAGE to determine if the temporary segment is really a sort segment? According to Jonathan Lewis in this Metalink thread (https://metalink.oracle.com/metalink/plsql/f?p=200:27:1683701837942099652::::p27_id,p27_show_header,p27_show_help:279353.999,1,1), V$SORT_USAGE will show more than just disk sorts. The sort segment in V$SORT_USAGE can be temporary tables, open cursors or some temporary LOBs, in which case the session statistic for 'sorts (disk)' would not be increased.

HTH,
Brian

-- 
===================================================================

Brian Peasland
oracle_dba_at_nospam.peasland.net
http://www.peasland.net

Remove the "nospam." from the email address to email me.


"I can give it to you cheap, quick, and good.
Now pick two out of the three" - Unknown