As a dba, I would like to know which of my database files are the worst to first in term of average cost of I/O. This helps me plan hardware upgrades or disk reorgs during down times. The worst disk may be a candidate for striping or that the stripe width or the total number of disks involved in the striping must be increased

If the average cost per I/O is high, this may indicate the need to increase the stripe width or the total number of disks involved in striping. Also, look at the total number of I/O\'s per transaction, I/O Rates per trans , then at how many oncurrent transactionsare running. For example: If each transaction requires 2 reads plus 1 write and expectation is to perform 1000 transactions persecond, this calculation requires that the system actually process 3000 I/Os per second. Therefore, if one disk can perform 50-100 I/Os per second, at least 60 disks will be needed (3000/50). Now if the requirement doubles from 1000 transactions per second to 2000, it will be necessary to double the number of disks.

To do this analysis we will calculate 2 values from our database

1. Average Time per Write : This represents the time spent per write during the sample data collection period

AvgTimePerWrite = deltaWriteTime / deltaPhysicalWrites

deltaWriteTime : difference between \"select writetim from v$filestat\" between the start and end of the sample period

deltaPhysicalWrites : differnece between \"select phywrts from v$filestat\" between sample end and start


2. Average Read Time : This represents the time spent per read during the sample data collection period

avgTimePerRead = deltaReadTime / deltaPhysicalReads

deltaReadTime : difference in \'select readtim from v$filestat\' between sample end and start

deltaPhysicalReads : difference in \'select phyrds from v$filestat\' between sample end and start


The above values should give you a very good idea of the performance of the disks / stripe sets in comparison to others and you can throw the ball back in the sysadmin\'s court for better performance on the io ;-)

u