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



BC Oracle tuning

Oracle training

Oracle support

Remote Oracle



  Defining the STATSPACK table

Oracle Tips by Burleson

Because the iostat utility is different on every server, we need to create separate versions of a shell script to capture the disk information. Regardless of the differences in display format, a single Oracle table can be defined to hold the iostat information. Here is the syntax for this table:

L 8-14

drop table perfstat.stats$iostat;

create table
snap_time          date,
elapsed_seconds    number(4),
hdisk              varchar2(8),
kb_read            number(9,0),
kb_write           number(9,0)
tablespace perfstat
storage (initial 20m next 1m )

create index
tablespace perfstat
storage (initial 5m next 1m)

create index
tablespace perfstat
storage (initial 5m next 1m)

Capturing the iostat Information

The get_iostat.ksh script is a UNIX shell script that collects disk-level I/O information at five-minute intervals. It runs the iostat utility and captures the output into the iostat table, using the data from the vol_grp table to create the sum_iostat table as well. Once you've run this script, you have the data required to identify your system's hot disks and mount points.


L 8-15


while true
   iostat -x  300 1|\
      sed 1,2d|\
      awk  '{ printf("%s %s %s\n", $1, $4, $5) }' |\

      if [ $VMSTAT_IO_R -gt 0 ] and [ $VMSTAT_IO_W -gt 0 ]
         sqlplus -s perfstat/perfstat <<!
         insert into
            (SYSDATE, 5, '$HDISK', $VMSTAT_IO_R,$VMSTAT_IO_W);
   sleep 300


Note that this script does not store iostat rows where the values for reads and writes are zero. This is because the stats$iostat table will grow very rapidly, and it is only useful to keep nonzero information. To keep the iostat utility running, you can add a script to your crontab file:

L 8-16


# First, we must set the environment . . . .
ORACLE_HOME=`cat /var/opt/oracle/oratab|grep $ORACLE_SID|cut -f2 -d':'`
MON=`echo ~oracle/iostat`

# If it is not running, then start it . . .
check_stat=`ps -ef|grep get_iostat|wc -l`;
oracle_num=`expr $check_stat`
if [ $oracle_num -ne 2 ]
 then nohup $MON/get_iostat_solaris.ksh > /dev/null 2>&1 &

Once the scripts are created, an entry can be placed into the crontab file to ensure that the iostat monitor is always running. Here is a sample of this crontab file:

L 8-17

# This is the daily iostat collector & report for the DBAs and SAs
00 * * * * /home/oracle/iostat/run_iostat_solaris.ksh > \ /home/oracle/iostat/r.lst

Generally, you should synchronize the STATSPACK snapshots and get_iostat.ksh, so that the file-level and disk-level data are collected during the same time periods. You can run both scripts as often as you like, and you can collect data over long periods of time without adverse effects on database performance. STATSPACK collects file I/O information very quickly from the Oracle database's system global area (SGA) memory. (The actual memory structures that contain the file I/O data are called v$filestat and file$.) The disk I/O data collection is also very fast, usually taking less than one second.

One drawback of this approach is that the data collection tables will eventually become very large. However, you can manage table size by deleting low I/O datafile entries. For example, you could delete inactive-file entries with the following SQL:

L 8-18

delete from perfstat.stats$iostat
where phys_read < 10 and phys_write < 10;

Bear in mind that deleting these entries will skew long-term averages, since the averages will be based only on higher-activity entries.

Although the information collected in stats$iostat and stats$filestatxs is somewhat redundant, the two sets of disk data complement each other. When the iostat results identify a hot mount point, you can turn to the stats$filestatxs results to look at the activity for each datafile residing on the mount point. The stats$filestatxs results also provide more-in-depth information, including the overall time required to perform the reads and writes. From elapsed-time information, you can quickly identify the files that are waiting on disk I/O and see the actual number of physical reads and writes.

Now that we see how to extend iostat for disk information, let's look at some other useful STATSPACK reports that can provide insight into our I/O subsystem.

Generating iostat Reports

Having a wealth of I/O data will be very useful in the process of disk load balancing, but this data is also useful for spotting trends. An application's disk access patterns can vary greatly according to daily or weekly processing needs, so the optimal file placement may not always be obvious. (For example, hdisk32 might be very busy during evening batch processing but largely idle during daytime processing.) And it's possible that relocating a datafile may relieve I/O contention for one process only to cause contention for an unrelated process.

In practice, disk load balancing takes several iterations of moving files to find the most workable overall file arrangement. Generally, however, the process of load balancing is well worth the time. Once you have achieved a fairly balanced load, you won't need to move the files unless new processes change the I/O pattern for the disks.

Nonetheless, isolating bottlenecks can be time-consuming and elusive. With the scripts and tables detailed in this section, you can quickly set up a procedure to provide the maximum information you can use for balancing your I/O load and minimizing disk I/O, which is key to keeping response times low. Strategies such as load balancing can go a long way toward improving the speed of your applications and keeping users happy across your network.

Using the I/O information from the stats$iostat table, you can generate trend and alert reports. Both types of reports are easy to generate using SQL, and the combination of the reports will help you identify current bottlenecks as well as spot potential future ones:

  • High disk I/O For each five-minute interval, this report displays the name of any Oracle database file with an I/O value—defined in this case by number of reads—that is more than 50 percent of the total I/O during that interval.

  • High file I/O The alert reports, on the other hand, are intended to identify current bottleneck possibilities—that is, specific datafiles experiencing a disproportionate amount of I/O (for example, those experiencing 20-percent more activity than the average for the mount point).

Often, companies use automated procedures to generate the alert reports and e-mail them to the DBA staff so that they can move these files to less-active mount points as soon as possible.

The following script will generate a sum of all of the I/O, summed by day, hour, or every five minutes.


L 8-19

column hdisk            format a10;
column mydate           format a15;
column sum_kb_read      format 999,999;
column sum_kb_write     format 999,999;

set pages 999;

break on hdisk skip 1;

--   to_char(snap_time,'yyyy-mm-dd HH24:mi:ss') mydate,
--   to_char(snap_time,'yyyy-mm-dd HH24') mydate,
   to_char(snap_time,'day') mydate,
   sum(kb_read)  sum_kb_read,
   sum(kb_write) sum_kb_write
group by
--  ,to_char(snap_time,'yyyy-mm-dd HH24:mi:ss')
--  ,to_char(snap_time,'yyyy-mm-dd HH24')

Here is the daily summary of disk activity from this script. Note that we see a clear picture of disk I/O activity by physical disk, and we see the changes by the
day of the week:

L 8-20

---------- --------------- ----------- ------------
atf0       tuesday                  33        1,749
           wednesday               150        7,950

atf2       tuesday                   0            4

atf289     tuesday                  33          330
           wednesday               150        1,500

atf291     tuesday                   0            0

atf293     tuesday                  32        1,696
           wednesday               150        7,950

atf4       tuesday                   0            0

atf6       tuesday                   1           10

atf8       tuesday                   0            0

sd0        tuesday                  96          160
           wednesday               450          750

Note that this script allows the display of iostat information using several different data formats:

L 8-21

to_char(snap_time,'yyyy-mm-dd HH24:mi:ss')
to_char(snap_time,'yyyy-mm-dd HH24')

To change the aggregation of the display information, simply substitute the date format. For example, to see the I/O aggregated by the hour of the day, we substitute the ‘day' format string with the ‘HH24' format string. Here is the same report aggregating by hour of the day:

L 8-22

---------- --------------- ----------- ------------
atf0       2001-12-26 21             9          477
           2001-12-26 22            12          636
           2001-12-26 23           112        14636
           2001-12-27 07           382         3636
           2001-12-27 08           433          641

atf2       2001-12-26 21             0            4

atf289     2001-12-26 21             9           90
           2001-12-26 22            12          120
           2001-12-26 23           132         5655

atf291     2001-12-26 21             0            0

atf293     2001-12-26 21             8          424
           2001-12-26 22            12          636
           2001-12-26 23           412         1646
           2001-12-27 00           574         4745
           2001-12-27 01           363         3736
           2001-12-27 02           332          432

atf4       2001-12-26 21            23           23

atf6       2001-12-26 21             1           10

atf8       2001-12-26 21             0            9

sd0        2001-12-26 21            24           40
           2001-12-26 22            36           60

Now that we see how we can collect iostat information from a STATSPACK extension table, let's examine how the Oracle DBA uses these reports to detect patterns in disk I/O.

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