A Quick and Simple Way to Measure the I/O
Contributions, Oracle

A Quick and Simple Way to Measure the I/O

The following post highlights the use of Oracle’s in-built I/O performance tool introduced in 11gR1. It is a part of the DBMS_RESOURCE_MANAGER (CALIBRATE_IO procedure) PL/SQL package. Though this post may not provide the complete picture of I/O in every respect, it is useful to quickly understand the maximum sustainable IOPs and average latency, particularly for OLTP workloads. Understanding the latency for single block reads is quite important since it directly impacts the performance of transaction-based queries that make use of index reads.

A few points:

  • Make sure ‘timed_statistics’ is true (there is no need for further changes if statistics_level is ‘typical’).
  • Set ‘filesystemio_options’ to either setall or async (restart of the instance may be required)
  • Run this process during off-hours since it will generate I/O and to avoid mix-ups with other things going on in the database.
  • If RAC is used, make sure all instances are up and running.

DBMS_RESOURCE_MANAGER.CALIBRATE_IO packages will take a couple of inputs, which can be left to default. There is no need to set up ‘resource plans’ for this procedure. For more information on this package, click here.

An example output –

SET SERVEROUTPUT ON;
declare
l_iops pls_integer;
l_mbps pls_integer;
l_latency pls_integer;
begin
DBMS_RESOURCE_MANAGER.CALIBRATE_IO (
max_iops => l_iops,
max_mbps => l_mbps,
actual_latency => l_latency);
dbms_output.put_line (‘Max I/O requests (per sec) = ‘ || l_iops);
dbms_output.put_line (‘Average latency (ms) = ‘ || l_latency);
dbms_output.put_line (‘Max thoroughput (mb/s) = ‘ || l_mbps);
end;
/
Max I/O requests = 1895
Average latency (ms) = 18
Max thoroughput (mb/s) = 268

PL/SQL procedure successfully completed.

Oracle will store the result in “DBA_RSRC_IO_CALIBRATE” view as well.

Do you have any tips or questions about this topic? Suggestions for our next posts? We would love to hear them! Click here to contribute to the DBA Stack community.

About The Author

Kishore Kottapalli

Kishore Kottapalli has been an Oracle DBA for 15 years, handling all areas of Oracle database administration. He has a special interest in Oracle performance tuning & NoSQL databases.