Home » Oracle » Oracle Database » How to calculate IOPS of an oracle database

How to calculate IOPS of an oracle database

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 in 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')
group by metric_name;

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.”

See also  Get Table,View, Materialized View, Package, Index Definition in Oracle

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,write,large write,large read , total bytes per sec etc


The information regarding IOPS can be found in different places of the AWR report

Instance Activity Stats
IOPS – (Input/Output Operations Per Second) – This is the sum of Physical Read Total IO Requests and Physical Write Total IO Requests

calculate IOPS in oracle database

IOPS            = Physical Read Total IO Requests + Physical Write Total IO Requests
                    = 83.10 + 361.28
                    = 444.38


IO Profile (starting with 11gR2)

See also  How to convert private key to ppk( Putty Format)

OPS – Total Requests (This value is the sum of the metrics Physical Read Total IO Requests Per Sec and Physical Write Total IO Requests Per Sec from the Instance Activity Stats area)

calculate IOPS in oracle database

Related Articles

How to turn on trace in Oracle
Oracle Explain Plan
Oracle Performance tuning Glossary
Oracle performance tuning Certification Quiz
Oracle Performance Tuning
https://docs.oracle.com/cd/B16240_01/doc/doc.102/e16282/osm_instance_help/osm_instance_instance_disk_performance_io_ps.html

2 thoughts on “How to calculate IOPS of an oracle database”

  1. Are you missing a GROUP BY METRIC_NAME on your SQL? Should be:

    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’)
    group by METRIC_NAME;

Leave a Comment

Your email address will not be published. Required fields are marked *

Scroll to Top