Data Management

Extend the capabilities of STATSPACK to measure Oracle disk I/O

The measurement of disk I/O plays a vital role in Oracle performance optimization. The technique outlined by our Oracle guru Don Burleson extends the capabilities of STATSPACK, generating a more complete set of statistics.

One of the current shortcomings of STATSPACK is that it cannot directly monitor disk input and output (I/O). Here's a great technique that I use for extending the capabilities of Oracle's STATSPACK performance utility to report statistics on I/O activity at the disk and file level in a UNIX environment.

Ordinarily, statistics captured by a STATSPACK snapshot are related only to the read and write activity at the Oracle data file level. These statistics are stored in the stats$filestatxs table. Normally, STATSPACK does not show I/O at the disk or mount point level, which can be valuable information in determining hyperactivity on particular files or disks.

UNIX, on the other hand, displays read and write I/O statistics only at the physical disk level, and it's the responsibility of the Oracle administrator to know what mount points and disks are used to store the Oracle data files. If DBAs segregate tables and indexes into separate tablespaces, they will know which objects reside in each file, and they can tell which tables and indexes are experiencing high I/O rates.

Instead of using standard utilities to generate a report for a single time period, you can modify utilities to collect I/O data over consistent intervals, storing the results in Oracle tables for easy access and reporting. The following is an outline of requirements:
  • File-level statistics—As mentioned, the STATSPACK stats$filestatxs table contains I/O data collected by snapshots taken at consistent intervals. I/O data captured includes the actual number of physical reads, physical writes, block reads, block writes, and the time required for each operation.
  • Disk-level statistics—By extending STATSPACK to capture disk I/O from the UNIX iostat command and placing the data in a newly created STATSPACK extension table (stats$iostat), we create a repository for expanded data reporting. The stats$iostat table will contain overall disk-level read and write information with corresponding timestamps. Using a script, we call get_iostat.ksh, and we collect the disk-level information displayed by the iostat command at regular intervals and insert it into the stats$iostat table.

Listing A is the sample script that I use in Solaris. Additionally, a cross-reference table (stats$vol_grp) between the stats$filestatxs and stats$iostat tables, could be created, linking mount points to physical disks. You would need to populate this table manually, according to how disks are partitioned into mount points. The design of these tables lets you aggregate, or average, I/O data over time and create summaries by disk, mount point, tablespace, or datafile.

Specific I/O activity
When the DBA prudently segregates Oracle objects into distinct datafiles and tablespaces, STATSPACK can be used to create useful reports showing individual I/O, selected datafiles, or groups of related datafiles. For example, the script in Listing B uses a filename "mask" to report on selected groups of related datafiles. If we have purposefully named our customer-related datafilescustomer.dbf, custhistory.dbf, and custorders.dbf, we can use this script to report the I/O history on all datafile names that contain the string "cust".

The output from this script (Listing C) shows the total read and write I/O per day for our custdatafiles. In order to better visualize what the report is telling us, we can graph the statistics using Excel's chart wizard, as shown in Figure A. By viewing the output graphically, we can often recognize patterns of activity called I/O signatures, which are useful in file load balancing.

Figure A
Graph the statistics.

The script in Listing D uses the newly captured disk-level statistics to display the sum of disk I/O by day, hour, or collection interval (five minutes in our earlier script). Notice how the script time interval can be selected via uncommenting appropriate lines of code. Listing E shows a partial listing of the report generated by Listing D.

Unlimited enhancements
I've provided a brief introduction to how the standard STATSPACK utility can be extended to collect and report on additional UNIX-provided information. Enhancing the reporting capabilities of STATSPACK is bound only by the imagination of the DBA in search of automating mundane tasks while providing timely and useful information.

For more information on STATSPACK, see these links:
Oracle9i documentation
Oracle Press STATSPACK Book

Editor's Picks