Call for Oracle support & training (800) 766-1884
Free Oracle Tips

Home
Corporate Oracle Training
Custom Oracle Training
Oracle New Features Training
Advanced Oracle DBA Classes
Oracle Tuning Courses
Oracle Tips & Tricks
Oracle Training Links
Oracle Training Links
Oracle Training Links

We are top for USA Oracle Training Clients

 

Free Oracle Tips


 
HTML Text AOL

Free Oracle App Server Tips


 
HTML Text

Oracle support

Oracle training

Oracle tuning

Rednecks!

Remote Oracle

Custom Oracle Training

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 Definitive Reference” 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

 

 

 
 
 

Oracle performance tuning book

 

 

Oracle performance tuning software

 
Oracle performance tuning software
 
Oracle performance Tuning 10g reference poster
 
Oracle training in Linux commands
 
Oracle training Excel
 
 
 
 
 
email BC:


Copyright © 1996 -  2014 by Burleson Inc. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.