How to calculate IOPS of an oracle database



Last updated on August 25th, 2016 at 10:27 am

What is IOPS
IOPS (Input/Output Operations Per Second, pronounced eye-ops) is a common performance measurement used to benchmark computer storage devices like hard disk drives (HDD), solid state drives (SSD), and storage area networks (SAN)
How to calculate IOPS of an oracle database
In Oracle database  we measure how much of such requests are generated by the database system. We have to check whether  our hardware are capable of processing the request generated by the database system in order to maintain the performance.IOPS is calculated in Oracle database as the sum of  “Physical Read Total IO Requests Per Sec” and “Physical Write Total IO Requests Per Sec”These values will be available in the table, dba_hist_sysmetric_summary

Here is a query on How to calculate IOPS of an oracle database

break on report
compute sum of Value on report
select METRIC_NAME,avg(AVERAGE) as “Value”
from dba_hist_sysmetric_summary
where METRIC_NAME in (‘Physical Read Total IO Requests Per Sec’,’Physical Write Total IO Requests Per Sec’)

Some important terms related to IOPS in metrics

Physical read IO requests – “Number of read requests for application activity (mainly buffer cache and direct load operation) which read one or more database blocks per request. This is a subset of “physical read total IO requests” statistic.”

Physical read total IO requests – “Number of read requests which read one or more database blocks for all instance activity including application, backup and recovery, and other utilities.”

Physical write IO requests – “Number of write requests for application activity (mainly buffer cache and direct load operation) which wrote one or more database blocks per request.”

Physical write total IO requests – “Number of write requests which wrote one or more database blocks from all instance activity including application activity, backup and recovery, and other utilities.”

physical read total multi block requests –“Number of large read requests which read multi database blocks for all instance activity ”

physical write total multi block requests -“Number of large write  requests which write multi database blocks for all instance activity ”

physical read total bytes -“Total bytes read  which read one or more database blocks for all instance activity including application, backup and recovery, and other utilities.”
physical write total bytes –“Total bytes write  which read one or more database blocks for all instance activity including application, backup and recovery, and other utilities.”
To calculate small reads:

Small Reads  = Total Reads – Large Reads
Small Writes = Total Writes – Large Writes
These metrics can be used  taken at two point of time can also be used to  calculate IOPS for small read,wrire,large write,large read , total bytes per sec etc


Leave a Reply