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-184.108.40.206.4.ELhugemem (ap6157rt) 06/18/2007
02:19:21 AM DEV tps rd_sec/s wr_sec/s
02:19:31 AM dev8-0 1.30 0.00 19.20
02:19:31 AM dev8-1 0.00 0.00 0.00
02:19:31 AM dev8-2 1.30 0.00 19.20
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
Linux 2.4.21-220.127.116.11.4.ELhugemem (ap6188rt) 06/18/2007
avg-cpu: %user %nice %sys %iowait %idle
7.54 0.00 6.37 0.14 85.94
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.
(f.readtim / decode(f.phyrds,0,-1,f.phyrds)) readtime,
(f.writetim / decode(f.phywrts,0,-1,phywrts)) writetime
d.file# = f.file#
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.