How to Calculate the Number of IOPS and Throughput of a Database (Doc ID 2206831.1)

 

GOAL

The goal of this article is to explain how to calculate the IOPS and throughput of a Database. 

SOLUTION

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

• Instance Activity Stats
• IO Profile (starting with 11gR2)
• Load Profile 

 

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 

Throughput - This is the sum of Physical read total bytes and Physical write total bytes

 

For the example above:

IOPS            = Physical Read Total IO Requests + Physical Write Total IO Requests
                    = 83.10 + 361.28
                    = 444.38
Throughput  = Physical read total bytes + Physical write total bytes
                    = 19,045,685.11 + 42,594,391.17
                    = 61640076.28 bytes
                    = 58.78 MB

 

IO Profile 

IOPS - 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)

Throughput in Mbps - Total (MB) (This value is the sum of the metrics Physical read total bytesc/sec and Physical read total bytes/sec from the AWR reports.)

 

The information in the "IO Profile" is just a summary of the information in "Instance Activity Stats".

Instance Activity Stats	IO Profile
physical read total IO requests	Total Requests: Read per Second
physical write total IO requests	Total Requests: Write Per Second
physical read total bytes / 1024 / 1024	Total (MB): Read per Second
physical write total bytes / 1024 /1024	Total (MB): Write Per Second
IOPS	Total Requests: Read+Write Per Second
Throughput (MB)	Total (MB): Read+Write Per Second

 

Load profile

 

Please note that the information displayed here is a subset of the one in the IO Profile area:

Load profile	Instance Activity Stats
Read IO requests	physical read IO requests
Write IO requests	physical write IO requests
Read IO(MB)	physical read bytes
Write IO(MB) How To Calculate IOPS of an Oracle Database IOPS (Input/Output Operations Per Second) is a common performance metric used to compare computer storage devices. IOPS in Oracle databases is the total number of read and write requests of the database system per second. To maintain database performance, we must check whether our hardware is capable of processing the request generated by the database system. These values ​​are in the dba_hist_sysmetric_summary table. Find the IOPS value of the instance(since its started): 1 2 3 SQL> 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') You can access the metric values between two dates by the following query: 1 2 3 4 5 6 7 SQL> select METRIC_NAME,avg(AVERAGE) value from dba_hist_sysmetric_summary where begin_time between to_date('16-FEB-17 00:00:00', 'dd-MON-yy hh24:mi:ss') and to_date('16-FEB-17 23:59:59', 'dd-MON-yy hh24:mi:ss') and end_time like '%16-FEB-17%' and METRIC_NAME in ('Physical Read Total IO Requests Per Sec','Physical Write Total IO Requests Per Sec') group by METRIC_NAME; You can also access all metric values from the Instance Activity Stats section of the AWR report.	physical write bytes