Oracle 11g I/O Calibration Feature.

Earlier in order to assess the I/O capabilities of the system
on which you would install Oracle database, there were
two options.

1)Use third party tools (Mercury loadRunner, Benchmark Factory etc).
2)Use ORION from Oracle.

Since Oracle database is to be installed on the system, it would be
much better to use ORION. Because unlike other third party
software’s that issue I/Os sequentially, ORION issues I/Os randomly
using Oracle datafiles which in turn gives a clear picture of the
actual database performance.

But now ORION is shipped with Oracle 11g named as Resource
Manager I/O calibration feature.


In order to work with this tool make sure following parameters
are set: -

disk_asynch_io = true

filesystemio_options = asynch

The first parameter is normally set to true, but the second 
parameter requires an instance restart.

After instance restart make sure that I/O is enabled for datafiles
by running the following query:

SQL> COL NAME FORMAT A50
SQL>select name, asynch_io from v$datafile f, v$iostat_file i
where f.file#=i.file_no
and (filetype_name=’Data File’ or filetype_name = ‘Temp File’)
/


Even if the ASYNC_ON for all the datafiles, the I/O calibration process
may not run. If there aren’t any asynch I/O slots available in the system
you might end up having a ORA-56708 error.

To find out the maximum number of asynch I/O slots:

cat  /proc/sys/fs/aio-max-nr

65536


To find out asynch slots consumed by the system:

cat /proc/sys/fs/aio-nr

7354

Only 7000 or so slots consumed, meaning plenty of slots available
for testing.

(If the asynch I/O slots consumed figure is close to the maximum slots
figure you might have to shutdown extra databases running in your
system.)

Parameters:
NUM_PHYSICAL_DISKS – Means the number of physical disks you have.
MAX_LATENCY – Means maximum latency for the disk access in milliseconds
MAX_IOPS – Maximum number of I/O operations per second.
MAX_MBPS – Maximum megabytes of I/O per second.
ACTUAL_LATENCY – actual latency reported during the I/O test.

SQL> set serveroutput on
SQL> declare
  lat   integer;
  iops  integer;
  mbps  integer;
begin

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;

/



##DBMS_RESOURCE_MANAGER.CALIBRATE_IO (number of disks,
##max_latency, iops, mbps, lat)

You can check the calibration status by issuing the following statement.

SQL> select * from v$io_calibration_status;

STATUS
-----------------
CALIBRATION_TIME
-----------------------------
IN PROGRESS


This may take a while for the test to complete.

Here is the output from my system.

max_iops = 183
latency =  11
max_mbps = 27


So, my system has a latency of 11 milliseconds. The system can handle a
maxiumum of 183 I/O operations per second with throughput of 27 MB/sec.

When running this test consider the following:

1)Don’t run the procedure multiple times across several databases
  that uses the same storage system.
2)Quiesce the database to minimize I/O on the instance.
3)For RAC make sure all instances are running.