Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 E-mail Us
 Oracle Articles
New Oracle Articles

 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog

 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Oracle Support

 SQL Tuning

 Oracle UNIX
 Oracle Linux
 Remote s
 Remote plans
 Application Server

 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S


 Consulting Staff
 Consulting Prices
 Help Wanted!


 Oracle Posters
 Oracle Books

 Oracle Scripts

Don Burleson Blog 









Oracle UNIX Defining the STATSPACK Table Administration

Oracle UNIX/Linux Tips by Burleson Consulting

Defining the STATSPACK table

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:

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.



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 the script above 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:


# 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. Below is a sample of this crontab file:

# 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:

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. Many experienced DBAs say that disk load balancing can be like pressing your fist into an overstuffed pillow: one area goes down, but another area bulges.

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.

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:

---------- --------------- ----------- ------------
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:

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:

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

atf2       2000-12-26 21             0            4

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

atf291     2000-12-26 21             0            0

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

atf4       2000-12-26 21            23           23

atf6       2000-12-26 21             1           10

atf8       2000-12-26 21             0            9

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

Now let?s wrap-up this chapter with a review of the major concepts regarding UNIX files with Oracle.


If you like Oracle tuning, see the book "Oracle Tuning: The Definitive Reference", with 950 pages of tuning tips and 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 Training at Sea
oracle dba poster

Follow us on Twitter 
Oracle performance tuning software 
Oracle Linux poster


Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


Copyright © 1996 -  2017

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.

Remote Emergency Support provided by Conversational