Oracle UNIX Administration Defining the STATSPACK Table 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
   perfstat.stats$iostat
(
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
   perfstat.stats$iostat_date_idx
on
   perfstat.stats$iostat
   (snap_time)
tablespace perfstat
storage (initial 5m next 1m)
;

create index
   perfstat.stats$iostat_hdisk_idx
on
   perfstat.stats$iostat
   (hdisk)
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.

Get_iostat_solaris.ksh
#!/bin/ksh

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

      if [ $VMSTAT_IO_R -gt 0 ] and [ $VMSTAT_IO_W -gt 0 ]
      then
         sqlplus -s perfstat/perfstat <<!
         insert into
            perfstat.stats\$iostat
         values
            (SYSDATE, 5, '$HDISK', $VMSTAT_IO_R,$VMSTAT_IO_W);
         exit
!
      fi
   done
   sleep 300

done

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:

#!/bin/ksh

# First, we must set the environment . . . .
ORACLE_SID=prodz1
ORACLE_HOME=`cat /var/opt/oracle/oratab|grep $ORACLE_SID|cut -f2 -d':'`
PATH=$ORACLE_HOME/bin:$PATH
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 &
fi

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.

rpt_disk.sql
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;

select
   hdisk,
--   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
from
   stats$iostat
group by
   hdisk
   ,to_char(snap_time,'day')
--  ,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:

HDISK      MYDATE          SUM_KB_READ SUM_KB_WRITE
---------- --------------- ----------- ------------
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

This output is invaluable for seeing when specific disk are encountering an inordinate amount of I/O. Note that this script allows the display of iostat information using several different data formats:

to_char(snap_time,'day')
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:

HDISK      MYDATE          SUM_KB_READ SUM_KB_WRITE
---------- --------------- ----------- ------------
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 that we see how we can collect iostat information from a STATSPACK extension table, let?s look at Oracle tuning factors that influence disk I/O.

 

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.