Donald K. Burleson Oracle Tips

Identifying high disk sorts

One very important area of Oracle tuning is determining when Oracle is doing too many disk sorts.  As we may know, the sort_area_size init.ora parameter controls the amount of RAM memory available to all connected tasks for sorting purposes.  When this area of RAM is exceeded, the task will invoke a disk sort in the temporary tablespace.  A disk sort is up to 15,000 times slower than an in-memory sort.

This short script will detect the overall sort activity for your Oracle database since it was started:

rem sorts.sql - displays in-memory and disk sorts

set pages 9999;

spool /tmp/sorts

column value format 999,999,999

select name, value from v$sysstat

  where name like 'sort%';

However, this does not give us trend information.  The following script is used to determine the times when your database has more than 100 disk sorts per hour.  In uses the STATSPACK utility to track the sorting behavior of Oracle on an hourly basis.

column sorts_memory  format 999,999,999

column sorts_disk    format 999,999,999

column ratio format .9999999999999

select

   to_char(snap_time,'yyyy-mm-dd HH24'),

   newmem.value-oldmem.value sorts_memory,

   newdsk.value-olddsk.value sorts_disk,

   (newdsk.value-olddsk.value)/(newmem.value-oldmem.value) ratio

from

   perfstat.stats$sysstat oldmem,

   perfstat.stats$sysstat newmem,

   perfstat.stats$sysstat newdsk,

   perfstat.stats$sysstat olddsk,

   perfstat.stats$snapshot   sn

where

   -- Where there are more than 100 disk sorts per hour

   newdsk.value-olddsk.value > 100

and

   snap_time > sysdate-&1

and

   newdsk.snap_id = sn.snap_id

and

   olddsk.snap_id = sn.snap_id-1

and

   newmem.snap_id = sn.snap_id

and

   oldmem.snap_id = sn.snap_id-1

and

   oldmem.name = 'sorts (memory)'

and

   newmem.name = 'sorts (memory)'

and

   olddsk.name = 'sorts (disk)'

and

   newdsk.name = 'sorts (disk)'

and

   newmem.value-oldmem.value > 0

;

Here is a sample of the output from this script.  Here is an invaluable tool for the Oracle professional to detect those times when their database is bombarded with large sorting requests.

TO_CHAR(SNAP_ SORTS_MEMORY   SORTS_DISK   RATIO
------------- ------------ ------------ ---------------
2001-02-04 08      6,425     141  .0219455252918
2001-02-04 09      6,539     145  .0221746444410
2001-02-04 10      6,413     149  .0232340558241
2001-02-04 11      6,407     141  .0220071796473
2001-02-05 12      6,473     149  .0230186930326
2001-02-05 10      6,475     147  .0227027027027
2001-02-05 11      6,572     145  .0220632988436
2001-02-06 10     90,385     130  .0014382917519
2001-02-06 11      6,648     147  .0221119133574