IO Cost Analysis for an Instance
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: IO Cost Analysis for an Instance

  1. #1
    Join Date
    Feb 2001
    Posts
    163
    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



  2. #2
    Join Date
    Aug 2001
    Location
    Orlando, FL
    Posts
    11

    No Values

    I am getting no values for WRITETIM or READTIM.
    Timed_statistics is on!
    Vinnie Salerno

  3. #3
    Join Date
    Aug 2001
    Posts
    390
    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 ???

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width