-
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
-
No Values
I am getting no values for WRITETIM or READTIM.
Timed_statistics is on!
-
Uday,
what happened if you have a very low percentage of WRITETIM/PHYWRTS let say 3/395663 or 44/331089 and the majority of the datafiles are at this rate.
What's causing it and how to resolve ???
