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


Oracle Consulting Support
Oracle Upgrades
Use New Oracle Features
Oracle Replication Support
Oracle Training
Remote Oracle DBA
System Documentation
Oracle Tips
Oracle Performance
 

Free Oracle Tips


 

HTML Text

BC Oracle tuning

Oracle training

Oracle support

Remote Oracle

 

 

   
 

Viewing I/O Signatures with STATSPACK


Oracle Tips by Burleson

You will find that your database will develop distinctive I/O signatures. The I/O signature for an OLTP database will be very different than that of a data warehouse, and you can use these I/O signatures to determine regular times when the disk I/O subsystem is overloaded. When we aggregate disk information by day of the week and hour of the day, we can see some very interesting patterns.

NOTE: When developing I/O signatures for your database, it is very important to begin at the global level and drill down for successive detail. For example, after running the global reports, you will find spikes in your database I/O during specific times. Your next step should be to isolate these I/O spikes to specific Oracle database files by closer inspection of the STATSPACK data, and running the rpt_io_pct.sql script to report on specific datafiles.

Let's begin by taking a look at a STATSPACK script to average disk read and write activity by the day of the week.

rpt_avg_io_dy.sql

L 8-23

set pages 9999;

column reads  format 999,999,999
column writes format 999,999,999

select
   to_char(snap_time,'day'),
   avg(newreads.value-oldreads.value) reads,
   avg(newwrites.value-oldwrites.value) writes
from
   perfstat.stats$sysstat oldreads,
   perfstat.stats$sysstat newreads,
   perfstat.stats$sysstat oldwrites,
   perfstat.stats$sysstat newwrites,
   perfstat.stats$snapshot   sn
where
   newreads.snap_id = sn.snap_id
and
   newwrites.snap_id = sn.snap_id
and
   oldreads.snap_id = sn.snap_id-1
and
   oldwrites.snap_id = sn.snap_id-1
and
  oldreads.statistic# = 40
and
  newreads.statistic# = 40
and
  oldwrites.statistic# = 41
and
  newwrites.statistic# = 41
having
   avg(newreads.value-oldreads.value) > 0
and
   avg(newwrites.value-oldwrites.value) > 0
group by
   to_char(snap_time,'day')
;

The output from the script will take a running average by the day of the week and display the output as follows:

L 8-24

TO_CHAR(S        READS       WRITES
--------- ------------ ------------
friday              72        2,093
monday             221        8,896
saturday           211        5,869
sunday             160        5,056
thursday           338        7,232
tuesday            603       11,765
wednesday          316        7,781

This output can then be pasted into an Excel spreadsheet, resequenced, and displayed using the Excel Chart Wizard. In Figure 8-14, we see the I/O signature for an Oracle database. Note that this signature clearly shows peak write activity on Mondays, Tuesdays, and Wednesdays. From this signature, the Oracle DBA knows that this database is loaded during the first part of each week.

Figure 8-52: Average I/O signature by day of the week

Now, let's see how easy it is to change this report to aggregate the data by the hour of the day. The script here is identical to the aggregate averages by day of the week except that the date format string has been changed from ‘day' to ‘HH24'.

rpt_avg_io_hr.sql

L 8-25

set pages 9999;

column reads  format 999,999,999
column writes format 999,999,999

select
   to_char(snap_time,'HH24'),
   avg(newreads.value-oldreads.value) reads,
   avg(newwrites.value-oldwrites.value) writes
from
   perfstat.stats$sysstat oldreads,
   perfstat.stats$sysstat newreads,
   perfstat.stats$sysstat oldwrites,
   perfstat.stats$sysstat newwrites,
   perfstat.stats$snapshot   sn
where
   newreads.snap_id = sn.snap_id
and
   newwrites.snap_id = sn.snap_id
and
   oldreads.snap_id = sn.snap_id-1
and
   oldwrites.snap_id = sn.snap_id-1
and
  oldreads.statistic# = 40
and
  newreads.statistic# = 40
and
  oldwrites.statistic# = 41
and
  newwrites.statistic# = 41
having
   avg(newreads.value-oldreads.value) > 0
and
   avg(newwrites.value-oldwrites.value) > 0
group by
   to_char(snap_time,'HH24')
;

Now, when we execute this script we see the read and write averages displayed by the hour of the day. Again, we can paste this output into a spreadsheet and create a graphical representation, thereby getting a visual picture of the I/O signature.

L 8-26

TO        READS       WRITES
-- ------------ ------------
00          250        6,103
02          180        4,701
03          174        4,580
04          195        5,832
05          191        5,109
06          171        4,669
07          221        4,727
08          354        5,353
09          264        9,531
10          258        7,994
11          249        7,397
12          364        8,499
13          341        7,902
14          326        8,288
15          305       10,891
16          279        9,019
17          692       17,291
18          592       10,444
19          448        9,911
20          385        8,247
21          395       11,405
22          366        9,182
23          271        7,308

The graph in Figure 8-15 (on the facing page) is a graphical representation of the I/O signature of physical reads by hour of the day. Here we see a clear daily trend where the read activity increases throughout the afternoon and a high peak of read activity every day at 6:00 p.m. This information can be extremely valuable to the Oracle DBA. In this example, the DBA could encourage the end-user community to direct their processing to periods before 5:00 p.m.

Figure 8-53: Oracle physical read activity averages by hour of the day

We can also plot the physical write activity in a graph, as shown in Figure 8-16. In this case, we see a gradual pattern of increasing writes to the database, peaking in the late afternoon. This pattern would be confirmed by an increase in the number of archived redo logs generated later in the day.

Figure 8-54: Oracle physical write activity averages by hour of the day


This is an excerpt from "Oracle High Performance tuning with STATSPACK" by Oracle Press.


If you like Oracle tuning, you may enjoy the new book "Oracle Tuning: The Definitive Reference", over 900 pages of BC's favorite tuning tips & scripts. 

You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.

  
 

Oracle performance tuning software 
 
 
 
 

Oracle performance tuning book

 

 
 
 
Oracle performance Tuning 10g reference poster
 
 
 
Oracle training in Linux commands
 
Oracle training Excel
 
Oracle training & performance tuning books
 

 

   

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

Oracle® is the registered trademark of Oracle Corporation. 

Hit Counter