José Valerio Oracle Technology

This utility was introduced in Oracle 11.1, it is an excellent tool to understand if your actual workload is overloading your disks or file systems. I/O is very important  in an Oracle database so I really recommend to all DBAs the use of this great utility to  know the actual capacity of your hardware. Below you will find an example that could be used in both versions 11.1 and 11.2.

Basic Knowledge

from Oracle documentation:

“The DBA can assess the I/O capability of the database’s storage system by using the PL/SQL function DBMS_RESOURCE_MANAGER.CALIBRATE_IO(). This routine issues a very I/O intensive read-only workload to the database’s files to assess the maximum IOPS (I/O requests per second) and MBPS (megabytes of I/O per second) that can be sustained. This data can be reexamined at any time using the DBA table, DBA_RSRC_IO_CALIBRATE, that stores I/O calibration results. The calibration should be performed when the database is idle and during off-peak hours to minimize the heavy I/O workload from interfering with the regular workload and vice versa. I/O calibration can be used to understand the performance of the storage subsystem and figure out whether I/O performance problems stem from the database or the storage subsystem. Unlike various external I/O calibration tools, this tool uses the Oracle code stack and issues I/O randomly rather than sequentially. The results, therefore, much more closely match the actual database performance”

How it works ?

Basically calling  to the CALIBRATE_IO procedure in the DBMS_RESOURCE_MANAGER package. It drives some I/O to the database files to test the throughput.

Why use this and not other vendor tool?

Because this tool uses Oracle database software libraries to drive the I/O; not some generic I/O generator. This capability is very important, remember, you are testing the I/O for an Oracle database..

Calibrating (on 11g R2)

First of all, you need to have asynch I/O capability otherwise this will fail.

Step 1

SQL> show parameters options
filesystemio_options string ASYNCH
filesystemio_options = asynch
SQL> show parameters disk
disk_asynch_io = true
filesystemio_options = asynch

Step 2

Before proceed  you will need to eval if your tablespaces support ASYNC_IO.

SELECT name, asynch_io
FROM v$datafile f,v$iostat_file i
WHERE f.file# = i.file_no
AND filetype_name = ‘Data File’
/

SQL> /
/oracle/app/oracle/oradata/R2/system01.dbf     ASYNC_ON
/oracle/app/oracle/oradata/R2/sysaux01.dbf     ASYNC_ON
/oracle/app/oracle/oradata/R2/undotbs01.dbf  ASYNC_ON
/oracle/app/oracle/oradata/R2/users01.dbf       ASYNC_ON
/oracle/app/oracle/oradata/R2/timesten_01.dbf         ASYNC_ON
/oracle/app/oracle/oradata/R2/goldengate.dbf        ASYNC_ON
/oracle/app/oracle/oradata/R2/FLOW_1044720297807647.dbf  ASYNC_ON
/oracle/app/oracle/oradata/R2/FLOW_1259909767198597.dbf   ASYNC_ON

Even if the above query returns ASYNC_ON and you may not be able to run the calibrate process probably you will need to check kernel settings to verify how many async slots have the system configured and how many are in use, to do this I did:

$ cat /proc/sys/fs/aio-max-nr    ->  To find out the maximum available async I/O slots
65536

$ cat /proc/sys/fs/aio-nr  -> To find out how many are being used
32768

Step 3

Now you are ready to  start the calibration process.  CALIBRATE_IO accepts two parameters:

• NUM_PHYSICAL_DISKS – is the number of physical disks you have. Remember: this is the number for disks, not LUNs.
• MAX_LATENCY – is the maximum latency for the disk access (in milliseconds)

vi calibrate_io.sql, add the following:

– dbms_resource_manager.calibrate_io(
– num_physical_disks IN PLS_INTEGER DEFAULT 1,
– max_latency IN PLS_INTEGER DEFAULT 20,
– max_iops OUT PLS_INTEGER,
– max_mbps OUT PLS_INTEGER,
– actual_latency OUT PLS_INTEGER);

SET SERVEROUTPUT ON
DECLARE
lat INTEGER;
iops INTEGER;
mbps INTEGER;
BEGIN
– DBMS_RESOURCE_MANAGER.CALIBRATE_IO (, , iops, mbps, lat);
DBMS_RESOURCE_MANAGER.CALIBRATE_IO (1, 10, iops, mbps, lat);  – (1 Disk, 20 ms max latency)
DBMS_OUTPUT.PUT_LINE (‘max_iops = ‘ || iops);
DBMS_OUTPUT.PUT_LINE (‘latency = ‘ || lat);
dbms_output.put_line(‘max_mbps = ‘ || mbps);
end;
/

Step 4

Depending of your hardware the process could take 3 minutes or more to complete, while running you can query a fixed view:

select * from v$io_calibration_status

When calibration is complete  it produces three OUT parameters, they are:

• MAX_IOPS – the maximum number of I/O Operations per second
• MAX_MBPS – the maximum Megabytes per second
• ACTUAL_LATENCY – the actual latency observed during the test

SQL> @calibrate_io

max_iops = 125
latency = 7
max_mbps = 44

PL/SQL procedure successfully completed.

Results were obtained running the calibration package on a personal laptop with the following characteristics:

• Hardware: LAPTOP HP 550 4GB 160 GB internal Disk, Core 2 Duo
• Software: Linux CentOS 64 Bits, 4 GB RAM

Of course your corporate hardware should be more fast.