Donald K. Burleson Oracle Tips

Using the UNIX iostat utility to find disk bottlenecks

Every Oracle administrator knows that disk I/O is the number one cause of performance problems.  However, some Oracle folks do not know how to identify disk bottlenecks.

One little-used UNIX utility can be very valuable for identifying disk I/O bottlenecks.  If you are not using a disk storage array such as EMC, the iostat utility can show you the actual I/O activity against each of your physical disks.

Here is a sample of the iostat command:

prompt> iostat 15

The “iostat 15” says to display one line of output for every disk that we have attached to our database server.  In this case we get output every 15 seconds, each showing the amount of I/O for each disk.

Here is a sample of the output:

Disks:        % tm_act     Kbps      tps    Kb_read   Kb_wrtn

hdisk6           1.0      73.8       4.2    8999945   3314136

hdisk7           0.7      86.4       3.1   13790876    629660

hdisk8           0.0       0.6       0.0      90469      2176

hdisk9           0.8      74.4       3.1    9875281   2532636

hdisk10          0.5      54.6       1.7    9108228      3616

hdisk11          0.1      13.7       0.4    2279793       244

Here are descriptions for the columns:

• % tm_act – The percentage of time that the disk was physically active.

• Kbps – The number of K-bytes transferred per second.

• Tps – the number of I/O requests to the disk.  Note that multiple logical I/O requests may be merged into a single physical request.

• Kb_read – The number of Kilobytes read during the interval.

• Kb_wrtn – The number of Kilobytes written during the time interval.

The “% tm_act” shows the percentage of active time for the disk, and this output, when used in conjunction with a utlestat report (report.txt) can show you disk bottlenecks.

If you like Oracle tuning, you might enjoy my latest book “Oracle Tuning: The Definitive Reference” by Rampant TechPress.  It’s only $41.95(I don’t think it is right to charge a fortune for books!) and you can buy it right now at this link:

http://www.rampant-books.com/book_2005_1_awr_proactive_tuning.htm