//
you're reading...
Administration

Database I/O Calibration

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’)
SQL> /

NAME                                                                                           ASYNCH_IO
———————————————————————-            ———
+DATA/sddev4/datafile/system.256.717334689                 ASYNC_ON
+DATA/sddev4/datafile/system.256.717334689                 ASYNC_ON
+DATA/sddev4/datafile/sysaux.257.717334691                 ASYNC_ON
+DATA/sddev4/datafile/undotbs1.258.717334693             ASYNC_ON
+DATA/sddev4/datafile/users.259.717334693                    ASYNC_ON
+DATA/sddev4/datafile/example.264.717335045               ASYNC_ON
+DATA/sddev4/datafile/undotbs2.265.717335643              ASYNC_ON
+DATA/sddev4/datafile/benchmark01.dbf                             ASYNC_ON

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
SQL> DECLARE
2  lat INTEGER;
3  iops INTEGER;
4  mbps INTEGER;
5  BEGIN
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);
11 end;
12  /
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.

Advertisements

About Hong Wang

I am an Oracle DBA, working in Oracle database since version 7.3. Worked in both application development and production support. Many experiences in real world complicate problems and database projects. This blog serves as a collections of notes I write on my database studies as well as issues I encountered/solved. Your comments are well welcome.

Discussion

Trackbacks/Pingbacks

  1. Pingback: » Database I/O Calibration - March 13, 2014

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: