Donald K. Burleson Oracle Tips

Using STATSPACK to track disk sorting

Monitoring the number of sorts to disk is an important Oracle DBA job. This report can be changed to send an alert when the number of disk sorts exceeds a predefined threshold, and we can also modify it to plot average sorts by hour of the day and day of the week. The script that follows computes average sorts, ordered by hour of the day:

set pages 9999;

column sorts_memory  format 999,999,999
column sorts_disk        format 999,999,999
column ratio                 format .99999

select
   to_char(snap_time,'HH24'),
   avg(newmem.value-oldmem.value) sorts_memory,
   avg(newdsk.value-olddsk.value) sorts_disk
from
   perfstat.stats$sysstat oldmem,
   perfstat.stats$sysstat newmem,
   perfstat.stats$sysstat newdsk,
   perfstat.stats$sysstat olddsk,
   perfstat.stats$snapshot   sn
where
   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
group by
   to_char(snap_time,'HH24')
;

Here is the output from the script. We can now take this data and create a graph in a spreadsheet.

TO SORTS_MEMORY   SORTS_DISK
-- ------------ ------------
00       18,855           11
01       19,546           15
02       10,128            5
03        6,503            8
04       10,410            4
05        8,920            5
06        8,302            7
07        9,124           27
08       13,492           71
09       19,449           55
10       19,812          106
11       17,332           78
12       20,566           76
13       17,130           46
14       19,071           61
15       19,494           68
16       20,701           79
17       19,478           44
18       23,364           29
19       13,626           20
20       11,937           17
21        8,467            7
22        8,432           10
23       11,587           10

Now, let’s look at how we can easily change the script to compute the averages by the day of the week.

set pages 9999;

column sorts_memory  format 999,999,999
column sorts_disk        format 999,999,999
column ratio                 format .99999

select
   to_char(snap_time,'day')       DAY,
   avg(newmem.value-oldmem.value) sorts_memory,
   avg(newdsk.value-olddsk.value) sorts_disk
from
   perfstat.stats$sysstat oldmem,
   perfstat.stats$sysstat newmem,
   perfstat.stats$sysstat newdsk,
   perfstat.stats$sysstat olddsk,
   perfstat.stats$snapshot   sn
where
   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
group by
   to_char(snap_time,'day')
;

Again, we will take the result set and plot it in a chart. This time, let’s plot the disk sorts.

DAY       SORTS_MEMORY   SORTS_DISK
--------- ------------ ------------
friday          12,545           54
monday          14,352           29
saturday        12,430            2
sunday          13,807            4
thursday        17,042           47
tuesday         15,172           78
wednesday       14,650           43

If you like Oracle tuning, you might enjoy my latest book “Oracle Tuning: The DefinitiveReference” by Rampant TechPress. (I don’t think it is right to charge a fortune for books!) and you can buy it right now at this link:

http://www.rampant-books.com/book_1002_oracle_tuning_definitive_reference_2nd_ed.htm