 |
|
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
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
L 8-15
#!/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 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
#!/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. 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.
rpt_disk.sql
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;
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:
L 8-20
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
Note that this script allows the display of
iostat information using several different data formats:
L 8-21
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:
L 8-22
HDISK
MYDATE SUM_KB_READ SUM_KB_WRITE
---------- --------------- ----------- ------------
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. |
|
