How to generate the IOPS report in Oracle database 19c
Posted by Mir Sayeed Hassan on September 15th, 2025
How to generate the IOPS report in Oracle database 19c
Note: The report is created as per the requirement of the client from the databaase., Usually we should do it on regular basis for the analysis of the data.
Check the database
SYS> select name, open_mode, version from V$instance, v$database; NAME OPEN_MODE VERSION --------- -------------------- ----------------- ORA19CDB READ WRITE 19.0.0.0.0
Method 1 : Throughput/IOPs
For Single instance database., Issue the below query.
SYS @ ora19cdb > SELECT value FROM v$parameter WHERE name = 'cluster_database'; VALUE -------------------------------------------------------------------------------- FALSE
Note: If VALUE = FALSE, it is a standalone (non-RAC) database.
SQL> select begin_time,end_time,round(maxval) maximum_iops,round(average) averate_iops,NUM_INTERVAL from DBA_HIST_SYSMETRIC_SUMMARY where begin_time between
to_date('01-aug-25 00:00:00','DD-mon_yy hh24:mi:ss') and to_date('15-aug-25 08:00:00','DD-mon_yy hh24:mi:ss') and METRIC_NAME='I/O Requests per Second' order by begin_time;
Output
BEGIN_TIM END_TIME MAXIMUM_IOPS AVERATE_IOPS NUM_INTERVAL
--------- --------- ------------ ------------ ------------
03-AUG-25 03-AUG-25 5 3 61
03-AUG-25 03-AUG-25 15 4 50
04-AUG-25 04-AUG-25 10 4 60
04-AUG-25 04-AUG-25 10 4 60
05-AUG-25 05-AUG-25 16 4 60
-------
-------
15-AUG-25 15-AUG-25 17 12 60
RAC database., you INSTANCE_NUMBER in where clause to get the result for RAC Nodes., Example Node1 Instance_number=1, Node2 Instance_number=2.
Check the RAC Database or not
SQL> SELECT value FROM v$parameter WHERE name = 'cluster_database'; VALUE -------------------------------------------------------------------------------- TRUE
Note: If VALUE = TRUE, the database is running in RAC mode.
Find out the instance number
SQL> SELECT instance_number, instance_name from gv$instance; INSTANCE_NUMBER INSTANCE_NAME --------------- ---------------- 1 racdb1 2 racdb2
SQL> select begin_time,end_time,round(maxval) maximum_iops,round(average) averate_iops,NUM_INTERVAL from DBA_HIST_SYSMETRIC_SUMMARY where begin_time between
to_date('01-sep-25 00:00:00','DD-mon_yy hh24:mi:ss') and to_date('15-sep-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;
BEGIN_TIM END_TIME MAXIMUM_IOPS AVERATE_IOPS NUM_INTERVAL
--------- --------- ------------ ------------ ------------
06-SEP-25 07-SEP-25 2178 322 60
07-SEP-25 07-SEP-25 467 186 60
07-SEP-25 07-SEP-25 937 190 60
07-SEP-25 07-SEP-25 2446 173 60
07-SEP-25 07-SEP-25 1315 244 60
07-SEP-25 07-SEP-25 1777 171 60
----------
----------
15-SEP-25 15-SEP-25 4199 2057 60
Method 2 : Transfer Rate/MBPS
This method will give you the result of the “transfer rate per secound” over a period of time.
For Single instance database., Issue the below query.
SQL> select begin_time,end_time,round(maxval) maximum_iops,round(average) averate_iops,NUM_INTERVAL from DBA_HIST_SYSMETRIC_SUMMARY where begin_time between
to_date('01-aug-25 00:00:00','DD-mon_yy hh24:mi:ss') and to_date('10-aug-25 08:00:00','DD-mon_yy hh24:mi:ss') and METRIC_NAME='I/O Megabytes per Second' order by begin_time;
BEGIN_TIM END_TIME MAXIMUM_IOPS AVERATE_IOPS NUM_INTERVAL
--------- --------- ------------ ------------ ------------
03-AUG-25 03-AUG-25 7 8 61
04-AUG-25 04-AUG-25 2 4 60
05-AUG-25 05-AUG-25 2 2 60
-----
-----
10-AUG-25 10-AUG-25 3 2 60
RAC database., you INSTANCE_NUMBER in where clause to get the result for RAC Nodes., Example Node1 Instance_number=1, Node2 Instance_number=2.
SQL> select begin_time,end_time,round(maxval) maximum_iops,round(average) averate_iops,NUM_INTERVAL from DBA_HIST_SYSMETRIC_SUMMARY where begin_time between
to_date('01-sep-25 00:00:00','DD-mon_yy hh24:mi:ss') and to_date('10-sep-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;
BEGIN_TIM END_TIME MAXIMUM_IOPS AVERATE_IOPS NUM_INTERVAL
--------- --------- ------------ ------------ ------------
06-SEP-25 07-SEP-25 107 14 60
07-SEP-25 07-SEP-25 28 3 60
----
----
10-SEP-25 10-SEP-25 41 3 60
IOSTAT
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; FILETYPE_NAME TOTAL_READ_MB TOTAL_WRITE_MB ------------------------------------------------------------- Other 0 18 Control File 26143847 349622 Log File 288881 30992 Archive Log 0 277485 Data File Backup 0 0 Data File Incremental Backup 0 0 Archive Log Backup 0 0 Data File Copy 0 0 Flashback Log 0 0 Data Pump Dump File 0 21 External Table 238 0 Data File 2007 9565 Temp File 646 642 Temp File 0 0 Data File 2084 22751 Data File 772 15561 Temp File 2 2 Data File 785 0 Data File 769 0 Data File 1029 1 Data File 769 0 Data File 1094 2312 Data File 888 1226 Data File 770 1956
Summary Query
SQL> 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');
READ_MB_PER_SEC WRITE_MB_PER_SEC TOTAL_MB_PER_SEC -------------------------------------------------------- .03 .01 .04
=====Hence tested and verified in our test env=====