How To Calculate IOPS of an Oracle Database

dbtut

6 years ago

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

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

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:

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;

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.