How to get IOPS from AWR and Real-time

Recently an interesting question came up in Oracle Database Performance Tuning forum on LinkedIn.

The question was:

How to calculate the IOPS from AWR Report. ?

With follow up question after my first SQL:

Is there any way to calculate IOPS in real time?
What views we should use to calculate IOPS real time.
And can we use gv$ASM_IOSTAT or gv$sysmetric.

I did little Google check and linked nice blog post by G.Makino about how to get needed information from AWR Report in EM: https://gmakino.wordpress.com/2012/11/08/how-to-identify-iops-in-awr-reports .

So I figured it would be nice to share some SQL in LinkedIn forum and here.

Get IOPS from AWR:

SELECT metric_name,
  (CASE WHEN metric_name LIKE '%Bytes%' THEN TO_CHAR(ROUND(MIN(minval / 1024),1)) || ' KB' ELSE TO_CHAR(ROUND(MIN(minval),1)) END) min,
  (CASE WHEN metric_name LIKE '%Bytes%' THEN TO_CHAR(ROUND(MAX(maxval / 1024),1)) || ' KB' ELSE TO_CHAR(ROUND(MAX(maxval),1)) END) max,
  (CASE WHEN metric_name LIKE '%Bytes%' THEN TO_CHAR(ROUND(AVG(average / 1024),1)) || ' KB' ELSE TO_CHAR(ROUND(AVG(average),1)) END) avg
FROM dba_hist_sysmetric_summary
WHERE metric_name
  IN ('Physical Read Total IO Requests Per Sec',
  'Physical Write Total IO Requests Per Sec',
  'Physical Read Total Bytes Per Sec',
  'Physical Write Total Bytes Per Sec')
GROUP BY metric_name
ORDER BY metric_name;

METRIC_NAME                                                      MIN                                         MAX                                         AVG ---------------------------------------------------------------- ------------------------------------------- ------------------------------------------- ------------------------------------------- Physical Read Total Bytes Per Sec                                0 KB                                        83453.8 KB                                  415.9 KB Physical Read Total IO Requests Per Sec                          0                                           881.9                                       13.3 Physical Write Total Bytes Per Sec                               0 KB                                        8014.9 KB                                   55 KB Physical Write Total IO Requests Per Sec                         0                                           901.1                                       3.8

Get real time IOPS (It’s good to point out, that there is no such view as gv$ASM_IOSTAT). There is view gv$asm_disk_stat, but its stats are cumulative. For real time sample we have to use gv$sysmetric (v$sysmetric):

SELECT inst_id,
  intsize_csec / 100 "Interval (Secs)",
  metric_name "Metric",
  (
  CASE
    WHEN metric_name LIKE '%Bytes%'
    THEN TO_CHAR(ROUND(AVG(value / 1024 ),1))
      || 'KB'
    ELSE TO_CHAR(ROUND(AVG(value),1))
  END) "Value"
FROM gv$sysmetric
WHERE metric_name IN ('Physical Read Total IO Requests Per Sec', 'Physical Write Total IO Requests Per Sec', 'Physical Read Total Bytes Per Sec', 'Physical Write Total Bytes Per Sec')
GROUP BY inst_id,
  intsize_csec,
  metric_name
ORDER BY inst_id,
  intsize_csec,
  metric_name;

   INST_ID Interval (Secs) Metric                                                           Value ---------- --------------- ---------------------------------------------------------------- ------------------------------------------          1           60.77 Physical Read Total Bytes Per Sec                                354.6KB          1           60.77 Physical Read Total IO Requests Per Sec                          16.4          1           60.77 Physical Write Total Bytes Per Sec                               27.8KB          1           60.77 Physical Write Total IO Requests Per Sec                         2.7          2            59.6 Physical Read Total Bytes Per Sec                                2460KB          2            59.6 Physical Read Total IO Requests Per Sec                          32.8          2            59.6 Physical Write Total Bytes Per Sec                               67.6KB          2            59.6 Physical Write Total IO Requests Per Sec                         3.4

You should be aware of fact that samples 60 seconds +/- on each node of RAC. So they are not exactly the same, but difference is not of a big deal.