ADVERTISEMENT

How to Generate IOPS and I/O Performance Reports in Oracle 19c

5 min read

This document explains how to generate IOPS and I/O throughput reports in Oracle Database 19c for both Single Instance and RAC databases. These reports are often requested by clients and should ideally be generated regularly for performance analysis and capacity planning.

Note:
The report examples below use today’s date (22-DEC-2025) for clarity.

Step 1: Check Database Name, Mode, and Version

SET LINES 200
SET PAGES 100

COLUMN name FORMAT A15
COLUMN open_mode FORMAT A12
COLUMN version FORMAT A15

SELECT name, open_mode, version
FROM v$instance, v$database;

Output

NAME            OPEN_MODE    VERSION
--------------- ------------ ---------------
ORA19CDB        READ WRITE   19.0.0.0.0

Step 2: Check Whether Database Is RAC or Single Instance

SET LINES 120
SET PAGES 50

COLUMN value FORMAT A10

SELECT value
FROM v$parameter
WHERE name = 'cluster_database';

Output (Single Instance)

VALUE
----------
FALSE

FALSE → Single Instance
TRUE → RAC Database

Method 1: IOPS Using Throughput (I/O Requests per Second)

Single Instance Database

SET LINES 200
SET PAGES 100

COLUMN begin_time FORMAT A18
COLUMN end_time FORMAT A18
COLUMN maximum_iops FORMAT 9999999
COLUMN average_iops FORMAT 9999999
COLUMN num_interval FORMAT 999

SELECT begin_time,
       end_time,
       ROUND(maxval)  AS maximum_iops,
       ROUND(average) AS average_iops,
       num_interval
FROM dba_hist_sysmetric_summary
WHERE begin_time BETWEEN
      TO_DATE('15-dec-25 00:00:00','DD-mon-yy hh24:mi:ss')
  AND TO_DATE('22-dec-25 08:00:00','DD-mon-yy hh24:mi:ss')
AND metric_name = 'I/O Requests per Second'
ORDER BY begin_time;

Output

BEGIN_TIME          END_TIME            MAXIMUM_IOPS  AVERAGE_IOPS  NUM_INTERVAL
------------------  ------------------  ------------- ------------- ------------
15-DEC-25 00:00     15-DEC-25 01:00     12            6             60
16-DEC-25 00:00     16-DEC-25 01:00     18            9             60
18-DEC-25 00:00     18-DEC-25 01:00     21            11            60
20-DEC-25 00:00     20-DEC-25 01:00     26            14            60
22-DEC-25 07:00     22-DEC-25 08:00     32            19            60

RAC Database – Instance-wise IOPS

Check RAC Instances

SET LINES 120
SET PAGES 50

COLUMN instance_name FORMAT A15

SELECT instance_number, instance_name
FROM gv$instance;

Output

INSTANCE_NUMBER  INSTANCE_NAME
---------------  ---------------
1                racdb1
2                racdb2

IOPS for RAC Instance 1

SET LINES 200
SET PAGES 100

COLUMN begin_time FORMAT A18
COLUMN end_time FORMAT A18
COLUMN maximum_iops FORMAT 9999999
COLUMN average_iops FORMAT 9999999

SELECT begin_time,
       end_time,
       ROUND(maxval)  AS maximum_iops,
       ROUND(average) AS average_iops,
       num_interval
FROM dba_hist_sysmetric_summary
WHERE begin_time BETWEEN
      TO_DATE('15-dec-25 00:00:00','DD-mon-yy hh24:mi:ss')
  AND TO_DATE('22-dec-25 08:00:00','DD-mon-yy hh24:mi:ss')
AND instance_number = 1
AND metric_name = 'I/O Requests per Second'
ORDER BY begin_time;

Output

BEGIN_TIME          END_TIME            MAXIMUM_IOPS  AVERAGE_IOPS  NUM_INTERVAL
------------------  ------------------  ------------- ------------- ------------
16-DEC-25 00:00     16-DEC-25 01:00     1540          420           60
18-DEC-25 00:00     18-DEC-25 01:00     2890          610           60
20-DEC-25 00:00     20-DEC-25 01:00     3725          985           60
22-DEC-25 07:00     22-DEC-25 08:00     4980          2130          60

Method 2: Transfer Rate (I/O Megabytes per Second)

Single Instance Database

SET LINES 200
SET PAGES 100

COLUMN begin_time FORMAT A18
COLUMN end_time FORMAT A18
COLUMN maximum_mbps FORMAT 999999
COLUMN average_mbps FORMAT 999999

SELECT begin_time,
       end_time,
       ROUND(maxval)  AS maximum_mbps,
       ROUND(average) AS average_mbps,
       num_interval
FROM dba_hist_sysmetric_summary
WHERE begin_time BETWEEN
      TO_DATE('15-dec-25 00:00:00','DD-mon-yy hh24:mi:ss')
  AND TO_DATE('22-dec-25 08:00:00','DD-mon-yy hh24:mi:ss')
AND metric_name = 'I/O Megabytes per Second'
ORDER BY begin_time;

Output

BEGIN_TIME          END_TIME            MAX_MBPS  AVG_MBPS  NUM_INTERVAL
------------------  ------------------  --------  --------  ------------
15-DEC-25 00:00     15-DEC-25 01:00     12        5         60
18-DEC-25 00:00     18-DEC-25 01:00     19        8         60
20-DEC-25 00:00     20-DEC-25 01:00     26        14        60
22-DEC-25 07:00     22-DEC-25 08:00     34        21        60

RAC – Transfer Rate per Instance

SET LINES 200
SET PAGES 100

SELECT begin_time,
       end_time,
       ROUND(maxval)  AS maximum_mbps,
       ROUND(average) AS average_mbps,
       num_interval
FROM dba_hist_sysmetric_summary
WHERE begin_time BETWEEN
      TO_DATE('15-dec-25 00:00:00','DD-mon-yy hh24:mi:ss')
  AND TO_DATE('22-dec-25 08:00:00','DD-mon-yy hh24:mi:ss')
AND instance_number = 1
AND metric_name = 'I/O Megabytes per Second'
ORDER BY begin_time;

IOSTAT – File-Level I/O Statistics

SET LINES 200
SET PAGES 100

COLUMN filetype_name FORMAT A30
COLUMN total_read_mb FORMAT 999999999
COLUMN total_write_mb FORMAT 999999999

SELECT filetype_name,
       small_read_megabytes + large_read_megabytes  AS total_read_mb,
       small_write_megabytes + large_write_megabytes AS total_write_mb
FROM v$iostat_file;

Summary: Real-Time I/O Throughput

SET LINES 150
SET PAGES 50

COLUMN read_mb_per_sec FORMAT 999.99
COLUMN write_mb_per_sec FORMAT 999.99
COLUMN total_mb_per_sec FORMAT 999.99

SELECT ROUND(SUM(CASE
           WHEN metric_name = 'Physical Read Total Bytes Per Sec'
           THEN value ELSE 0 END) / 1024 / 1024, 2) AS read_mb_per_sec,
       ROUND(SUM(CASE
           WHEN metric_name = 'Physical Write Total Bytes Per Sec'
           THEN value ELSE 0 END) / 1024 / 1024, 2) AS write_mb_per_sec,
       ROUND(SUM(CASE
           WHEN metric_name IN ('Physical Read Total Bytes Per Sec',
                                'Physical Write Total Bytes Per Sec')
           THEN value ELSE 0 END) / 1024 / 1024, 2) AS total_mb_per_sec
FROM v$sysmetric
WHERE metric_name IN ('Physical Read Total Bytes Per Sec',
                      'Physical Write Total Bytes Per Sec');

Output

READ_MB_PER_SEC  WRITE_MB_PER_SEC  TOTAL_MB_PER_SEC
---------------  ----------------  -----------------
0.45             0.18              0.63

Final Notes

  • Always check RAC vs Single Instance before generating reports
  • Use instance-level metrics for RAC
  • Generate IOPS reports regularly, not only on client request
  • Combine AWR + IOSTAT + SYS metrics for accurate analysis
Close ✖