Disk Sorts - A Subtle Threat to Database Performance - Page 2
June 6, 2003by Robin Schumacher
Are Disk Sorts Affecting Your Database?
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:
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