There are several ways to check the IO statistics and load on the devices. We have OS level utilities like iostat and sar. We will check out first the OS utilities and then we will move to check IO stats using database query.

Example of using sar -d for IO stats:

(appmgr01) appmgr – -bash $ sar -d 10 5
Linux 2.4.21-37.0.0.1.4.ELhugemem (ap6157rt)    06/18/2007

02:19:31 AM       DEV       tps  rd_sec/s  wr_sec/s
02:19:41 AM    dev8-0      0.50      0.00     12.81
02:19:41 AM    dev8-1      0.00      0.00      0.00
02:19:41 AM    dev8-2      0.50      0.00     12.81

Here we can see that dev8-0, dev8-1 and dev8-2 are the devices (disks).

tps is the transfer rate/second in terms of number of requests
rd_sec/s is Number  of  sectors  read  from the device. The size of a sector is 512 bytes.
wr_sec/s is Number  of  sectors  write  to the device. The size of a sector is 512 bytes.

Another command is iostat

-bash-2.05b$ iostat
Linux 2.4.21-37.0.0.1.4.ELhugemem (ap6188rt)    06/18/2007

Device:            tps   Blk_read/s   Blk_wrtn/s   Blk_read   Blk_wrtn
sda               1.21         6.16        15.18   19695596   48524898
sda1              0.94         6.15        10.84   19655648   34666374
sda2              0.26         0.01         4.33      39404   13858522

Here you can check the %user column, which will show you the percentage of CPU that occurred while executing at the user level (application).

Other significant things are already explained in Performace Tuning Oracle Database 10g

You can check the IO stats using some SQLs on dynamic views as given below. This query will provide you with the read/write statistics for each file of database.

SELECT d.name,
f.phyrds reads,
f.phywrts wrts,
(f.readtim / decode(f.phyrds,0,-1,f.phyrds)) readtime,
(f.writetim / decode(f.phywrts,0,-1,phywrts)) writetime
FROM
v$datafile d,
v$filestat f
WHERE
d.file# = f.file#
ORDER BY
d.name;

NAME                                                    READS       WRTS   READTIME   WRITETIME
————————————————– ———- ———- ———-   ———-
/slot/ems1149/oracle/db/apps_st/data/tx_idx20.dbf          70        129   .9         .031007752
/slot/ems1149/oracle/db/apps_st/data/tx_idx21.dbf          46         72   .6521739   .041666667
/slot/ems1149/oracle/db/apps_st/data/tx_idx22.dbf         151        428   .7814569   .044392523
/slot/ems1149/oracle/db/apps_st/data/tx_idx23.dbf         107        300   1.177570        .08
/slot/ems1149/oracle/db/apps_st/data/tx_idx24.dbf         177        935   .4293785   .098395722
/slot/ems1149/oracle/db/apps_st/data/tx_idx3.dbf          103       1624   1.427184   .580049261

v$filestat view gives the read/write statistics for datafiles. Here phyrds are the number of physical reads done from respective datafiles and phywrts are the number of physical writes down by DBWR process in writting from buffer cache to disk. Also readtime, what we are calculating here is the average read time per request. Similarly the writetime.

Excessive disk reads and write time shows that you have I/O contention. Here are some of the point which you can make a note in order to reduce I/O contention.

• Increase the number of disks in the storage system.
• Separate the database and the redo log files.
• For a large table, use partitions to reduce I/O.
• Stripe the data either manually or by using a RAID disk-striping system.
• Invest in cutting-edge technology, such as file caching, to avoid I/O bottlenecks.
• Consider using the new Automatic Storage Management system.