Disk calibrate_io tips

Oracle Database Tips by Donald BurlesonJanuary 16, 2015

Oracle calibrate_io tips

The dbms_resource_manager package has a nice procedure called calibrate_io which is perfect for calibrating disk latency and system throughout. Also see these additional notes on dbms_resource_manager.calibrate_io and the dba_rsrc_io_calibrate view.

You can pass several arguments to dbms_resource_manager.calibrate_io:

· Maximum latency tolerance - This is the I/O per second target for the disks
(milliseconds)

· Number of disk spindles - The number of physical disk devices

The Oracle docs provide this working example of the calibrate_io procedure. Note the use of dbms_output.put_line to display the disk calibration results:

SET SERVEROUTPUT ON

DECLARE

lat INTEGER;

iops INTEGER;

mbps INTEGER;

BEGIN

-- DBMS_RESOURCE_MANAGER.CALIBRATE_IO (<DISKS>, <MAX_LATENCY>, iops, mbps, lat);

DBMS_RESOURCE_MANAGER.CALIBRATE_IO (2, 10, iops, mbps, lat);

DBMS_OUTPUT.PUT_LINE ('max_iops = ' || iops);

DBMS_OUTPUT.PUT_LINE ('latency = ' || lat);

dbms_output.put_line('max_mbps = ' || mbps);

end;

After running this procedure you can view the results using the v$io_calibration_status and for RAC, the gv$io_calibration_status views.

You can view the output on the xxx view, showing important disk speed metrics:

Maximum I/O per second for the disk (or group of disks)
Maximum throughout (in megabytes per second)
Average Disk response time (in milliseconds)

SELECT * FROM dba_rsrc_io_calibrate;

START_TIME END_TIME MAX_IOPS MAX_MBPS MAX_PMBPS LATENCY DISKS

-------------------- -------- ---------- ---------- ---------- --------

28-JUL-2008 28-JUL-2008 556 48 48 17 1

Oracle Training from Don Burleson

The best on site "Oracle training classes" are just a phone call away! You can get personalized Oracle training by Donald Burleson, right at your shop!

��