Measuring Oracle I/O performance using DBMS_RESOURCE_MANAGER.CALIBRATE_IO
May 2, 2013 Leave a comment
Environment: Oracle database 220.127.116.11.0, Oracle Linux 6.4
Oracle 11g has a procedure named “CALIBRATE_IO” which you can use to measure the I/O performance of your Oracle system. You can use this procedure to evaluate disk subsystem layout or configuration changes.
The procedure is part of the DBMS_RESOURCE_MANAGER PL/SQL package. Only users with the SYSDBA privilege can execute this procedure. You also need to enable ASYNCH_IO for your datafiles.
Note: do not run this on a live production system. For optimal results, use this on a quiet system with no other users connected.
This is an example with user SYS as SYSDBA (you have to enable DBMS_OUTPUT to see the results):
DECLARE lat INTEGER; iops INTEGER; mbps INTEGER; BEGIN DBMS_RESOURCE_MANAGER.CALIBRATE_IO( 1 /* # of disks */ , 10 /* maximum tolerable latency in milliseconds */ , iops /* I/O rate per second */ , mbps /* throughput, MB per second */ , lat /* actual latency in milliseconds */ ); DBMS_OUTPUT.PUT_LINE('max_iops = ' || iops); DBMS_OUTPUT.PUT_LINE('latency = ' || lat); DBMS_OUTPUT.PUT_LINE('max_mbps = ' || mbps); END; /
On my system, the total execution time was about 8 minutes.
The output is as follows:
max_iops = 2588
latency = 8
max_mbps = 241
The results can also be found in the system table DBA_RSRC_IO_CALIBRATE.