{"id":5034,"date":"2026-01-11T12:46:54","date_gmt":"2026-01-11T07:16:54","guid":{"rendered":"https:\/\/w3buddy.com\/?p=5034"},"modified":"2026-01-20T10:14:00","modified_gmt":"2026-01-20T04:44:00","slug":"how-to-generate-iops-and-i-o-performance-reports-in-oracle-19c","status":"publish","type":"post","link":"https:\/\/w3buddy.com\/blog\/how-to-generate-iops-and-i-o-performance-reports-in-oracle-19c\/","title":{"rendered":"How to Generate IOPS and I\/O Performance Reports in Oracle 19c"},"content":{"rendered":"\n

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

\n

Note:<\/strong>
The report examples below use today\u2019s date (22-DEC-2025)<\/strong> for clarity.<\/p>\n<\/blockquote>\n\n\n\n

Step 1: Check Database Name, Mode, and Version<\/h2>\n\n\n\n
SET LINES 200\nSET PAGES 100\n\nCOLUMN name FORMAT A15\nCOLUMN open_mode FORMAT A12\nCOLUMN version FORMAT A15\n\nSELECT name, open_mode, version\nFROM v$instance, v$database;<\/code><\/pre>\n\n\n\n
Output<\/h3>\n\n\n\n
NAME            OPEN_MODE    VERSION\n--------------- ------------ ---------------\nORA19CDB        READ WRITE   19.0.0.0.0<\/code><\/pre>\n\n\n\n
Step 2: Check Whether Database Is RAC or Single Instance<\/h2>\n\n\n\n
SET LINES 120\nSET PAGES 50\n\nCOLUMN value FORMAT A10\n\nSELECT value\nFROM v$parameter\nWHERE name = 'cluster_database';<\/code><\/pre>\n\n\n\n
Output (Single Instance)<\/h3>\n\n\n\n
VALUE\n----------\nFALSE<\/code><\/pre>\n\n\n\n
\n
FALSE<\/code> \u2192 Single Instance
TRUE<\/code> \u2192 RAC Database<\/p>\n<\/blockquote>\n\n\n\n
Method 1: IOPS Using Throughput (I\/O Requests per Second)<\/h1>\n\n\n\n
Single Instance Database<\/h2>\n\n\n\n
SET LINES 200\nSET PAGES 100\n\nCOLUMN begin_time FORMAT A18\nCOLUMN end_time FORMAT A18\nCOLUMN maximum_iops FORMAT 9999999\nCOLUMN average_iops FORMAT 9999999\nCOLUMN num_interval FORMAT 999\n\nSELECT begin_time,\n       end_time,\n       ROUND(maxval)  AS maximum_iops,\n       ROUND(average) AS average_iops,\n       num_interval\nFROM dba_hist_sysmetric_summary\nWHERE begin_time BETWEEN\n      TO_DATE('15-dec-25 00:00:00','DD-mon-yy hh24:mi:ss')\n  AND TO_DATE('22-dec-25 08:00:00','DD-mon-yy hh24:mi:ss')\nAND metric_name = 'I\/O Requests per Second'\nORDER BY begin_time;<\/code><\/pre>\n\n\n\n
Output<\/h3>\n\n\n\n
BEGIN_TIME          END_TIME            MAXIMUM_IOPS  AVERAGE_IOPS  NUM_INTERVAL\n------------------  ------------------  ------------- ------------- ------------\n15-DEC-25 00:00     15-DEC-25 01:00     12            6             60\n16-DEC-25 00:00     16-DEC-25 01:00     18            9             60\n18-DEC-25 00:00     18-DEC-25 01:00     21            11            60\n20-DEC-25 00:00     20-DEC-25 01:00     26            14            60\n22-DEC-25 07:00     22-DEC-25 08:00     32            19            60<\/code><\/pre>\n\n\n\n
RAC Database \u2013 Instance-wise IOPS<\/h2>\n\n\n\n
Check RAC Instances<\/h3>\n\n\n\n
SET LINES 120\nSET PAGES 50\n\nCOLUMN instance_name FORMAT A15\n\nSELECT instance_number, instance_name\nFROM gv$instance;<\/code><\/pre>\n\n\n\n
Output<\/h3>\n\n\n\n
INSTANCE_NUMBER  INSTANCE_NAME\n---------------  ---------------\n1                racdb1\n2                racdb2<\/code><\/pre>\n\n\n\n
IOPS for RAC Instance 1<\/h3>\n\n\n\n
SET LINES 200\nSET PAGES 100\n\nCOLUMN begin_time FORMAT A18\nCOLUMN end_time FORMAT A18\nCOLUMN maximum_iops FORMAT 9999999\nCOLUMN average_iops FORMAT 9999999\n\nSELECT begin_time,\n       end_time,\n       ROUND(maxval)  AS maximum_iops,\n       ROUND(average) AS average_iops,\n       num_interval\nFROM dba_hist_sysmetric_summary\nWHERE begin_time BETWEEN\n      TO_DATE('15-dec-25 00:00:00','DD-mon-yy hh24:mi:ss')\n  AND TO_DATE('22-dec-25 08:00:00','DD-mon-yy hh24:mi:ss')\nAND instance_number = 1\nAND metric_name = 'I\/O Requests per Second'\nORDER BY begin_time;<\/code><\/pre>\n\n\n\n
Output<\/h3>\n\n\n\n
BEGIN_TIME          END_TIME            MAXIMUM_IOPS  AVERAGE_IOPS  NUM_INTERVAL\n------------------  ------------------  ------------- ------------- ------------\n16-DEC-25 00:00     16-DEC-25 01:00     1540          420           60\n18-DEC-25 00:00     18-DEC-25 01:00     2890          610           60\n20-DEC-25 00:00     20-DEC-25 01:00     3725          985           60\n22-DEC-25 07:00     22-DEC-25 08:00     4980          2130          60<\/code><\/pre>\n\n\n\n
Method 2: Transfer Rate (I\/O Megabytes per Second)<\/h1>\n\n\n\n
Single Instance Database<\/h2>\n\n\n\n
SET LINES 200\nSET PAGES 100\n\nCOLUMN begin_time FORMAT A18\nCOLUMN end_time FORMAT A18\nCOLUMN maximum_mbps FORMAT 999999\nCOLUMN average_mbps FORMAT 999999\n\nSELECT begin_time,\n       end_time,\n       ROUND(maxval)  AS maximum_mbps,\n       ROUND(average) AS average_mbps,\n       num_interval\nFROM dba_hist_sysmetric_summary\nWHERE begin_time BETWEEN\n      TO_DATE('15-dec-25 00:00:00','DD-mon-yy hh24:mi:ss')\n  AND TO_DATE('22-dec-25 08:00:00','DD-mon-yy hh24:mi:ss')\nAND metric_name = 'I\/O Megabytes per Second'\nORDER BY begin_time;<\/code><\/pre>\n\n\n\n
Output<\/h3>\n\n\n\n
BEGIN_TIME          END_TIME            MAX_MBPS  AVG_MBPS  NUM_INTERVAL\n------------------  ------------------  --------  --------  ------------\n15-DEC-25 00:00     15-DEC-25 01:00     12        5         60\n18-DEC-25 00:00     18-DEC-25 01:00     19        8         60\n20-DEC-25 00:00     20-DEC-25 01:00     26        14        60\n22-DEC-25 07:00     22-DEC-25 08:00     34        21        60<\/code><\/pre>\n\n\n\n
RAC \u2013 Transfer Rate per Instance<\/h2>\n\n\n\n
SET LINES 200\nSET PAGES 100\n\nSELECT begin_time,\n       end_time,\n       ROUND(maxval)  AS maximum_mbps,\n       ROUND(average) AS average_mbps,\n       num_interval\nFROM dba_hist_sysmetric_summary\nWHERE begin_time BETWEEN\n      TO_DATE('15-dec-25 00:00:00','DD-mon-yy hh24:mi:ss')\n  AND TO_DATE('22-dec-25 08:00:00','DD-mon-yy hh24:mi:ss')\nAND instance_number = 1\nAND metric_name = 'I\/O Megabytes per Second'\nORDER BY begin_time;<\/code><\/pre>\n\n\n\n
IOSTAT \u2013 File-Level I\/O Statistics<\/h1>\n\n\n\n
SET LINES 200\nSET PAGES 100\n\nCOLUMN filetype_name FORMAT A30\nCOLUMN total_read_mb FORMAT 999999999\nCOLUMN total_write_mb FORMAT 999999999\n\nSELECT filetype_name,\n       small_read_megabytes + large_read_megabytes  AS total_read_mb,\n       small_write_megabytes + large_write_megabytes AS total_write_mb\nFROM v$iostat_file;<\/code><\/pre>\n\n\n\n
Summary: Real-Time I\/O Throughput<\/h1>\n\n\n\n
SET LINES 150\nSET PAGES 50\n\nCOLUMN read_mb_per_sec FORMAT 999.99\nCOLUMN write_mb_per_sec FORMAT 999.99\nCOLUMN total_mb_per_sec FORMAT 999.99\n\nSELECT ROUND(SUM(CASE\n           WHEN metric_name = 'Physical Read Total Bytes Per Sec'\n           THEN value ELSE 0 END) \/ 1024 \/ 1024, 2) AS read_mb_per_sec,\n       ROUND(SUM(CASE\n           WHEN metric_name = 'Physical Write Total Bytes Per Sec'\n           THEN value ELSE 0 END) \/ 1024 \/ 1024, 2) AS write_mb_per_sec,\n       ROUND(SUM(CASE\n           WHEN metric_name IN ('Physical Read Total Bytes Per Sec',\n                                'Physical Write Total Bytes Per Sec')\n           THEN value ELSE 0 END) \/ 1024 \/ 1024, 2) AS total_mb_per_sec\nFROM v$sysmetric\nWHERE metric_name IN ('Physical Read Total Bytes Per Sec',\n                      'Physical Write Total Bytes Per Sec');<\/code><\/pre>\n\n\n\n
Output<\/h3>\n\n\n\n
READ_MB_PER_SEC  WRITE_MB_PER_SEC  TOTAL_MB_PER_SEC\n---------------  ----------------  -----------------\n0.45             0.18              0.63<\/code><\/pre>\n\n\n\n
Final Notes<\/h2>\n\n\n\n
    \n
  • Always check RAC vs Single Instance<\/strong> before generating reports<\/li>\n\n\n\n
  • Use instance-level metrics<\/strong> for RAC<\/li>\n\n\n\n
  • Generate IOPS reports regularly<\/strong>, not only on client request<\/li>\n\n\n\n
  • Combine AWR + IOSTAT + SYS metrics<\/strong> for accurate analysis<\/li>\n<\/ul>\n","protected":false},"excerpt":{"rendered":"
    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\u2019s date (22-DEC-2025) for clarity. Step 1: Check Database […]<\/p>\n","protected":false},"author":1,"featured_media":5036,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"googlesitekit_rrm_CAowu461DA:productID":"","footnotes":""},"categories":[1225],"tags":[],"class_list":["post-5034","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-database"],"_links":{"self":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/posts\/5034","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/comments?post=5034"}],"version-history":[{"count":1,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/posts\/5034\/revisions"}],"predecessor-version":[{"id":5035,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/posts\/5034\/revisions\/5035"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/media\/5036"}],"wp:attachment":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/media?parent=5034"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/categories?post=5034"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/tags?post=5034"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}