Monday, May 13, 2013

IOPS check from Oracle 11 server

If you want to check Oracle database server IOPS (Input/Output Operations Per Second) usage you can do it with following SQL:

SQL> break on report
SQL> compute sum label TOTAL_IOPS: of IOPS on report
SQL> select METRIC_NAME,avg(AVERAGE) as "IOPS" 

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;

For example:
SQL> break on report
SQL> compute sum label TOTAL_IOPS: of IOPS on report
SQL> select METRIC_NAME,avg(AVERAGE) as "IOPS"
  2     from dba_hist_sysmetric_summary
  3     where METRIC_NAME in ('Physical Read Total IO Requests Per Sec','Physical Write Total IO Requests Per Sec')
  4     group by METRIC_NAME;

METRIC_NAME                                                            IOPS
---------------------------------------------------------------- ----------
Physical Write Total IO Requests Per Sec                                  32.8448519
Physical Read Total IO Requests Per Sec                                  13.3675773
                                                                                                 ----------
TOTAL_IOPS:                                                                         46.2124292


NOTE! DBA_HIST_SYSMETRIC_SUMMARY contains snapshots of V$SYSMETRIC_SUMMARY view. V$SYSMETRIC_SUMMARY displays a summary of all system Metric values for the long-duration system metrics. The average, maximum value, minimum value, and the value of one standard deviation for the last hour are displayed for each metric item. And because of this if you are using V$SYSMETRIC_SUMMARY instead of DBA_HIST_SYSMETRIC_SUMMARY you will get different results .

No comments:

Post a Comment