DOYENSYS Knowledge Portal




We Welcome you to visit our DOYENSYS KNOWLEDGE PORTAL : Doyensys Knowledge Portal




Saturday, December 31, 2016

Find IOPS of an Oracle database

There may be a situation where we have to calculate IOPS (Input Output Per Second) of oracle database to know the performance bottleneck of an oracle database regarding IO or when planning capacity for new hardware implementation. Though we can find it from AWR report, below is the sql to calculate the IOPS of an Oracle database

IOPS for entire day:
====================

ALTER SESSION SET NLS_DATE_FORMAT='DD-MON-YY HH24:MI:SS';
break on report
compute sum of value on report
select METRIC_NAME,avg(AVERAGE) value from dba_hist_sysmetric_summary
where begin_time between to_date('20-DEC-16 00:00:00', 'dd-MON-yy hh24:mi:ss') and to_date('20-DEC-16 23:59:59', 'dd-MON-yy hh24:mi:ss')
and end_time like '%20-DEC-16%' and  METRIC_NAME in ('Physical Read Total IO Requests Per Sec','Physical Write Total IO Requests Per Sec')
group by METRIC_NAME;

You can change the time interval in the above sql query based on your requirement.


No comments: