DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2001
    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 ;-)


  2. #2
    Join Date
    Aug 2001
    Orlando, FL

    No Values

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

  3. #3
    Join Date
    Aug 2001

    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

We have made updates to our Privacy Policy to reflect the implementation of the General Data Protection Regulation.