Monitoring UNIX disk I/O

Monitoring disk I/O is a very important UNIX task that is generally performed by the UNIX systems administrator or the Oracle DBA.  While those you purchase powerful disk array systems such as EMC are limited to proprietary tools such as Open Symmetric Manager or Navistar, those using generic disk can use the iostat utility to monitor disk over time.

Since iostat measures I/O at the physical disk level, the Oracle DBA must create the mapping between the physical disks, the UNIX mount points, and the Oracle files.  Let’s see how this is done.

Building the Oracle File-to-Disk Architecture

If you are not using a block-level block striping mechanism such as RAID 0+1, it is a good idea to map each physical disk spindle directly to a UNIX mount point. For example, here is a sample mapping for a set of triple-mirrored disks:

By mapping the UNIX mount points directly to physical disks, it becomes easy to know the disk location of a hot Oracle datafile. For example, if our STATSPACK hot file report (in the statspack_alert.sql script) indicates that /u03/oradata/prod/books.dbf is consuming an inordinate amount of I/O, we immediately know that /u03 is getting hit, and that /u03 maps directly to disk hdisk33 and its mirrored disks.

Please note that this mapping technology becomes more complex because of the large size of disk spindles. The trend has been toward creating very large disks, and it is not uncommon to find disks that range from 36GB to 72GB. In these cases, many small Oracle databases will reside on a single physical disk, and load balancing becomes impractical. However, this large-disk issue does not imply that the DBA should abandon disk monitoring simply because all of the files reside on a single disk. Remember, high file I/O can be corrected with the judicious use of the Oracle data buffers. For example, a hot table can be moved into the KEEP pool, thereby caching the data blocks and relieving the hot-disk issue.

It is interesting to note that some products such as EMC have developed methods to internally detect hot files and transparently move them to cooler disks. However, this approach has a problem. Blindly moving a hot datafile to a cooler disk is analogous to pressing into an overstuffed pillow: one area goes in, but another areas bulges.

It is never simple in the real world. In the real world, the Oracle DBA may find a specific range of data blocks within a datafile that is getting high I/O, and they will segregate these blocks onto a separate datafile. This relates to the point we made earlier in this chapter that the Oracle DBA must always segregate hot tables and indexes onto separate tablespaces.

If you are not using RAID 0+1 or RAID 5, it is simple to write a dictionary query that will display the mapping of tablespaces-to-files and files-to-UNIX mount points. Note that the data selected from the dba_data_files view relies on using the Oracle Optimal Flexible Architecture (OFA). If we use the OFA, the first four characters of the filename represents the UNIX mount point for the file. We can also adjust the substring function in the query below to extract the filename without the full disk path to the file.

Reporting on the Oracle Disk Architecture

If your shop follows the OFA standard, you can write a dictionary query that will report on the disk-to-file mapping for your database. This script assumes that you use OFA names for your datafiles (e.g., /u02/oradata/xxx.dbf), and that your UNIX mount points map to easily identifiable physical disks. The script below queries the dba_data_files view and reports the mapping.

rpt_disk_mapping.sql
set pages 999;
set lines 80; 

column mount_point heading 'MP'; 

break on mount_point skip 2; 

select
   substr(file_name,1,4) mount_point,
   substr(file_name,21,20) file_name,
   tablespace_name
from
   dba_data_files
group by
   substr(file_name,1,4),
   substr(file_name,21,20) ,
   tablespace_name
;

Here is the output from this script. Please note that there is a one-to-one correspondence between Oracle tablespaces, physical datafiles, and UNIX mount points.

MP   FILE_NAME            TABLESPACE_NAME
---- -------------------- ------------------------------
/u02 annod01.dbf          ANNOD
     arsd.dbf             ARSD
     bookd01.dbf          BOOKD
     groupd01.dbf         GROUPD
     pagestatsd01.dbf     PAGESTATSD
     rdruserd01.dbf       RDRUSERD
     subscrd01.dbf        SUBSCRD
     system01.dbf         SYSTEM
     userstatsd01.dbf     USERSTATSD 

/u03 annox01.dbf          ANNOX
     bookx01.dbf          BOOKX
     groupx01.dbf         GROUPX
     pagestatsx01.dbf     PAGESTATSX
     perfstat.dbf         PERFSTAT
     rbs01.dbf            RBS
     rdruserx01.dbf       RDRUSERX
     subscrx01.dbf        SUBSCRX
     temp01.dbf           TEMP
     tools01.dbf          TOOLS
     userstatsx01.dbf     USERSTATSX

Extending STATSPACK for Disk I/O Data

Our data collection approach relies on I/O information from Oracle and from the physical disks. We will start by using existing STATSPACK tables, but we will also extend STATSPACK to add the disk I/O information. We will use the UNIX iostat utility to capture detailed disk I/O because almost every dialect of UNIX has the iostat utility. However, there is a dialect issue. Just as vmstat has different dialects, iostat is slightly different in each version of UNIX, and the Oracle DBA will need to customize a data collection mechanism according to his or her requirements. Even within the same dialect, there are arguments that can be passed to the iostat utility that change the output display.

The UNIX iostat utility

The UNIX iostat command syntax looks like this:

For example, to request five samples, spaced at 10 seconds apart, we would issue the command as follows:

Unlike the vmstat utility when all of the data is displayed on one line, the iostat output will have many lines per snapshot, one for each physical disk. Let’s begin by taking a short tour of the different dialects of the iostat command. We will begin by showing differences between iostat for Solaris and HP/UX, and then show a method for extending STATSPACK to capture STATSPACK data for AIX servers.

Here we see each of the disks displayed on one line. For each disk we see:

* The percentage tm_act

* The Kbytes per second of data transfer

* The number of disk transactions per second

* The number of Kbytes read and written during the snapshot period.

In the HPUX output we see the following columns:

* Device name

* Kilobytes transferred per second

* Number of seeks per second

* Milliseconds per average seek

Unlike the iostat output for HP/UX, here we see each disk presented horizontally across the output. We see disks sd0, sd1, sd6 and sd35.

The -x option of the HP/UX iostat utility changes the output from vertical to horizontal. For each disk, we report the reads per second, writes per second, and percentage disk utilization.

Now that we see the differences between the dialects of iostat, let’s see how this information can be captured into STATSPACK extension tables.