How can you tell if disk sorts are occurring on your system and causing an adverse effect on the performance of your database? There are a couple of routes you can take to determine if disk sorts are indeed present on your database:

• Examine the memory/disk sort ratio
• View wait events that may indicate disk sort activity
• Check session activity regarding disk sort operations

The place to start is with a ratio of memory vs. disk sorts to see if disk sorts are coming into play on your database. The basic query to use is the following:

SELECT ROUND((100 * b.VALUE) /DECODE ((a.VALUE + b.VALUE), 
       0,1,(a.VALUE + b.VALUE)),2) "Percent memory sorts"
FROM   v$sysstat a, 
       v$sysstat b
WHERE  a.name = 'sorts (disk)'
AND    b.name = 'sorts (memory)'
 
Percent memory sorts
-------------------
95.5
 

Note that if Oracle has been up a long time, the cumulative numbers for memory sorts may hide more recent occurrences of disk sorts. What you hope to see (whether just using cumulative metrics or delta-based statistics) is a high value - at least 95% or more. Lower percentages could indicate a current or growing disk sort problem.

From a wait event perspective, there aren't really wait events that are solely devoted to disk sort activity. However, from past experience I can tell you that databases I've been involved with that suffered from disk sort activity seem to show a high percentage of time waited for the direct path read and direct path write events. A wait-based query you can use to check for these events is the following:

select event,
       total_waits,
       round(100 * (total_waits / sum_waits),2) pct_waits,
       time_wait_sec,
       round(100 * (time_wait_sec / greatest(sum_time_waited,1)),2)
       pct_time_waited,
       total_timeouts,
       round(100 * (total_timeouts / greatest(sum_timeouts,1)),2) 
       pct_timeouts,
       average_wait_sec
from
(select event,
       total_waits,
       round((time_waited / 100),2) time_wait_sec,
       total_timeouts,
       round((average_wait / 100),2) average_wait_sec
from sys.v_$system_event
where event in ('direct path read','direct path write')),
(select sum(total_waits) sum_waits,
        sum(total_timeouts) sum_timeouts,
        sum(round((time_waited / 100),2)) sum_time_waited
 from sys.v_$system_event
 where event not in 
 ('lock element cleanup', 
 'pmon timer', 
 'rdbms ipc message',
 'rdbms ipc reply',
 'smon timer', 
 'SQL*Net message from client', 
 'SQL*Net break/reset to client',
 'SQL*Net message to client',
 'SQL*Net more data from client',
 'dispatcher timer',
 'Null event',
 'parallel query dequeue wait',
 'parallel query idle wait - Slaves',
 'pipe get',
 'PL/SQL lock timer',
 'slave wait',
 'virtual circuit status',
 'WMON goes to sleep') AND 
 event not like 'DFS%' AND 
 event not like 'KXFX%')
order by 2 desc, 1 asc  

Output from a system suffering from disk sorts might look like this:

Notice the high percentages of time waited attributed to these two events over all other events.

One other thing you can do at the global database level is to check the I/O activity of your temporary tablespaces that are devoted to disk sort operations. A query like this can be used:

select  c.name tablespace_name,
        sum(b.phyrds),
        sum(b.phywrts),
        sum(b.phyblkrd),
        sum(b.phyblkwrt),
        sum(b.readtim),
        sum(b.writetim) 
from    sys.v_$datafile a, 
        sys.v_$filestat b,
        sys.ts$ c ,
        sys.file$ d
where   ( a.file# = b.file#) and 
        ( a.file# = d.file#) and
        d.ts# = c.ts# 
group by c.name 
union all
select  c.name tablespace_name,
        sum(b.phyrds),
        sum(b.phywrts),
        sum(b.phyblkrd),
        sum(b.phyblkwrt),
        sum(b.readtim),
        sum(b.writetim) 
from    sys.v_$tempfile a,
        sys.v_$tempstat b,
        sys.ts$ c ,
        sys.x$kccfn v, 
        sys.x$ktfthc hc 
where   a.file# = b.file# and 
        a.file# = hc.ktfthctfno and
        hc.ktfthctsn = c.ts# and
        v.fntyp = 7 and
        v.fnnam is not null  and 
        v.fnfno = hc.ktfthctfno  and 
        hc.ktfthctsn = c.ts#
group by c.name
order by 1

Output from a database with high disk sort activity might look like this:

Notice the high numbers of reads and writes for the TEMP and TEMP2 tablespaces above. This indicates a lot of disk sort activity. You should also check the read and write times to see if Oracle is experiencing I/O delays in reading from and writing to the temporary tablespaces.

From the global database level, you can move into examining disk sort activity at the session level to see if one or more sessions are causing the bulk of disk sorts. A query I like to use for this is the following:

select  b.sid sid,
        decode (b.username,null,e.name,b.username) user_name,
        b.machine machine_name,
        to_char(logon_time,'dd-mon-yy hh:mi:ss pm') logon_time,
        sum(decode(c.name,'sorts (disk)',value,0)) disk_sorts,
        sum(decode(c.name,'sorts (memory)',value,0)) memory_sorts,
        sum(decode(c.name,'sorts (rows)',value,0)) rows_sorted,
        round(100 * (sum(decode(c.name,'sorts (disk)',value,0)) /
        greatest(sum(decode(c.name,
        'sorts (memory)',value,0)),1,
        sum(decode(c.name,'sorts (memory)',value,0)))),2)
        disk_memory_sort_ratio
from    sys.v_$sesstat a,
        sys.v_$session b,
        sys.v_$statname c, 
        sys.v_$bgprocess e
where   a.statistic#=c.statistic# and 
        b.sid=a.sid  and 
        e.paddr (+) = b.paddr  and
        c.name in ('sorts (disk)',
                 'sorts (memory)',
                 'sorts (rows)')
group by b.sid, 
         decode (b.username,null,e.name,b.username),
         b.machine, 
         to_char(logon_time,'dd-mon-yy hh:mi:ss pm') 
order by 5 desc,6 desc  

From the query's output, you can see what sessions may be responsible for disk sort actions.

Finally, to see if any disk sorts are in progress on your database, you can use this query (for Oracle 8.0 and higher) to view the SQL statements causing current disk sorts along with other associated information:

select sql_text,
       sid,
       c.username,
       machine,
       tablespace,
       extents,
       blocks
from   sys.v_$sort_usage a,
       sys.v_$sqlarea b,
       sys.v_$session c
where  a.sqladdr = b.address and
       a.sqlhash = b.hash_value and
       a.session_addr = c.saddr
order by sid

Previous   Next

Back to DBAsupport.com