There are several new features in 11g Oracle Resource Manager, I/O calibration is one of them. This feature enables user to assess the performance of the storage subsystem, and determine whether I/O performance problems are caused by the database or the storage subsystem. Unlike other external I/O calibration tools that issue I/Os sequentially, the I/O calibration feature of Oracle Database issues I/Os randomly using Oracle datafiles to access the storage media, producing results that more closely match the actual performance of the database.
The I/O calibration is done by calling DBMS_RESOURCE_MANAGER.CALIBRATE_IO procedure. This procedure generates an I/O intensive read-only workload to the datafiles to determine the maximum IOPS, and MBPS that can be sustained by the storage subsystem. For an Oracle Real Application Clusters (RAC) database, the workload is simultaneously generated from all instances.
To run the calibration, asynchronous I/O must be enabled. This is controled by init.ora parameter FILESYSTEMIO_OPTIONS. It should be set to ASYNC or SETALL.
Before running the calibration check each datafile and temp file to see asynchronous I/O is enable for each file by running below query:
SQL> set pages 300 lines 140
SQL> col name for a70
SQL> select name,asynch_io from v$datafile f,v$iostat_file i
SQL> where f.file#=i.file_no
SQL> and (filetype_name=’Data File’ or filetype_name=’Temp File’)
DBMS_RESOURCE_MANAGER.CALIBRATE_IO procedure takes two inputs: number of physical disks (default is 1) and maximum tolerable latency in milliseconds for database-block-sized I/O request (default is 20). The outputs from the call of procedure are:
1) Maximum number of I/O request per second that can be sustained. The I/O requests are randomly-distributed, database-block-sized reads.
2) Maximum throughput of I/O that can be sustained, expressed in megabytes per second. The I/O request are randomly distributed, 1 megabyte reads.
3) Average latency of database-block-sized I/O requests at max_iops rate, expressed in milliseconds.
Below is an example of calling the procedure to do I/O calibration. My ASM disk group for the data files has three disks, therefore 3 for the 1st input. The intended latency is 10 milliseconds.
SQL> SET SERVEROUTPUT ON
2 lat INTEGER;
3 iops INTEGER;
4 mbps INTEGER;
6 DBMS_RESOURCE_MANAGER.CALIBRATE_IO (NUM_PHYSICAL_DISKS =>3, MAX_LATENCY => 20, MAX_IOPS => iops, MAX_MBPS => mbps, ACTUAL_LATENCY => lat);
8 DBMS_OUTPUT.PUT_LINE (‘max_iops = ‘ || iops);
9 DBMS_OUTPUT.PUT_LINE (‘latency = ‘ || lat);
10 dbms_output.put_line(‘max_mbps = ‘ || mbps);
max_iops = 2869
latency = 11
max_mbps = 519
PL/SQL procedure successfully completed.
You can always query DBA_RSRC_IO_CALIBRATE to view the calibration results.
Row Number Column Name Value
============ ============================ ==============================
1 END_TIME : 03-APR-12 03.35.33.988817 PM
1 LATENCY : 11
1 MAX_IOPS : 2869
1 MAX_MBPS : 519
1 MAX_PMBPS : 532
1 NUM_PHYSICAL_DISKS : 3
1 START_TIME : 03-APR-12 03.18.04.409679 PM
The results indicate:
Maximum number of data-block read requests that can be sustained per second is 2869.
Maximum megabytes per second of maximum-sized read requests that can be sustained is 519.
Maximum megabytes per second of large I/O requests that can be sustained by a single process is 532.
Latency for data-block read requests is 11.