Make sure the database files are properly laid out to avoid hot spots. Monitor I/O activities using operating system commands such as iostat and sar. Pay attention to disk queue length, disk service time, and I/O throughput. If a device is particularly busy, then consider relocating some of the data files that are on the device. On the Solaris operating system, you can get I/O statistics on controllers and devices with the iostat dxnC command. However, hot spots tuning is easier said than done. You need to know how the application uses I/O. Furthermore, if the application is immature and new functionalities are constantly being added, the hot spots may be moving targets. DBAs are normally not apprised of new developments and often have to discover them reactively. This is why I/O balancing can be a never ending task. If you can upgrade to Oracle Database 10g, ASM (Automatic Storage Management) can help with I/O balancing.

By the way, in addition to the systemwide db file sequential read average wait time from the V$SYSTEM_EVENT view, Oracle also provides single-block read statistics for every database file in the V$FILESTAT view. The file-level single-block average wait time can be calculated by dividing the SINGLEBLKRDTIM with the SINGLEBLKRDS, as shown next. (The SINGLEBLKRDTIM is in centiseconds.) You can quickly discover which files have unacceptable average wait times and begin to investigate the mount points or devices and ensure that they are exclusive to the database.

select a.file#, 
       b.file_name, 
       a.singleblkrds, 
       a.singleblkrdtim, 
       a.singleblkrdtim/a.singleblkrds average_wait
from   v$filestat a, dba_data_files b 
where  a.file# = b.file_id   
and    a.singleblkrds > 0
order by average_wait;


FILE# FILE_NAME                     SINGLEBLKRDS SINGLEBLKRDTIM AVERAGE_WAIT
----- ----------------------------- ------------ -------------- ------------ 
  367 /dev/vgEMCp113/rPOM1P_4G_039          5578            427   .076550735
  368 /dev/vgEMCp113/rPOM1P_4G_040          5025            416    .08278607
  369 /dev/vgEMCp113/rPOM1P_4G_041         13793           1313   .095193214
  370 /dev/vgEMCp113/rPOM1P_4G_042          6232            625   .100288832
  371 /dev/vgEMCp113/rPOM1P_4G_043          4663            482   .103366931
  372 /dev/vgEMCp108/rPOM1P_8G_011        164828         102798   .623668309
  373 /dev/vgEMCp108/rPOM1P_8G_012        193071         125573    .65039804
  374 /dev/vgEMCp108/rPOM1P_8G_013        184799         126720   .685717996
  375 /dev/vgEMCp108/rPOM1P_8G_014        175565         125969   .717506337

html" alt="Team LiB" width="62" height="15">