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.”
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
IOPS = Physical Read Total IO Requests + Physical Write Total IO Requests
= 83.10 + 361.28
= 444.38
IO Profile (starting with 11gR2)
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)
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
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;
you are correct. I have modified my post