I/O Calibration using DBMS_RESOURCE_MANAGER.CALIBRATE_IO

Author: Hari Muthuswamy

One of the important functions of an Oracle DBA is to pay close attention to database performance. During tuning exercises, Oracle DBAs often encounter I/O Subsystem bottlenecks, which can cause an increase in response times and time-outs. A calibration of the storage subsystem will reveal any performance limitations. Oracle provides two approaches to calibrating the I/O subsystem:

1. Calibration from inside the database using the DBMS_RESOURCE_MANAGER.CALIBRATE_IO

2. Calibration from outside the database using the Oracle Orion Calibration tool. 

While DBMS_RESOURCE_MANAGER.CALIBRATE_IO requires a database, the Orion Calibration tool does not require a database or a software installation.

Oracle’s Real Application Testing (RAT) tool can also be used to impose a realistic load on the database using its capture and replay approach.

DBMS_RESOURCE_MANAGER.CALIBRATE_IO

CALIBRATE_IO measures only read workloads. To be precise, it measures the latency, maximum throughput for read sequential IO and maximum IOPS for random read I/O. The results is a simple output of averages. So it is not ideal for storage systems with combination of disks and SSD cache. It helps in getting a quick idea about storage subsystems without caches. We will explore other options for measuring storage performance in upcoming blogs.

PROCEDURE CALIBRATE_IO
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 NUM_PHYSICAL_DISKS             BINARY_INTEGER          IN     DEFAULT
 MAX_LATENCY                    BINARY_INTEGER          IN     DEFAULT
 MAX_IOPS                       BINARY_INTEGER          OUT
 MAX_MBPS                       BINARY_INTEGER          OUT
 ACTUAL_LATENCY                 BINARY_INTEGER          OUT

NUM_PHYSICAL_DISKS is an optional parameter and, when specified, the calibration runs more efficiently. By specifying an approximate number of physical disks, it will provide a faster and accurate calibration.

MAX_LATENCY is an optional parameter that can be set when there is a specific target latency. MAX_LATENCY measures the maximum tolerable latency in milliseconds.

The CALIBRATE_IO performs calibration in 2 steps:

Step 1

The out parameters MAX_IOPS and ACTUAL_LATENCY are populated. MAX_IOPS is an important value for OLTP systems as it represents the maximum iops a database can sustain. It accomplishes this by issuing random db_block-sized reads (Default 8k) to all the data files of the database. ACTUAL_LATENCY represents the workload’s average latency.

Step 2

The out parameter MAX_MBPS is populated. MAX_MBPS is an important value for DSS systems as it represents the maximum mbps of I/O aF database can sustain. It accomplishes this by issuing random MB reads to all the data files of the database.

Limitations

1. Should be run when the database is idle and at off-peak hours.
2. Only one calibration can be performed on databases that share a storage subsystem.
3. Quiesce the database to minimize I/O on the instance.
4. For Oracle RAC Clusters, ensure all instances are open to calibrate the storage subsystem across nodes.
5. Workload is simultaneously generated from all instances.

Prerequisites
1. Must be a SYSDBA
2. TIMED_STAISTICS must be set to TRUE
3. Asynchronous I/O must be enabled.

– When using file systems, asynchronous I/O can be enabled by setting the FILESYSTEMIO_OPTIONS initialization parameter to SETALL. Setting disk_async_io to true will enable async IO. In Linux, async I/O will be disabled if the maximum number of I/O slots are consumed. In this case the below query will return async to off.

– Output of the following query will denote if ASYNCH_IO is turned on for various datafiles.

COL NAME FORMAT A50
SELECT NAME,ASYNCH_IO FROM V$DATAFILE F,V$IOSTAT_FILE I
WHERE  F.FILE#=I.FILE_NO
AND    FILETYPE_NAME IN ('Data File','Temp File');

Additionally, only one calibration can be performed on a database instance at a time.

Below is an example of running a I/O Calibration

SET SERVEROUTPUT ON

DECLARE
  lat  INTEGER;
  iops INTEGER;
  mbps INTEGER;
BEGIN
-- DBMS_RESOURCE_MANAGER.CALIBRATE_IO (, , 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;
/

This process will take several minutes to finish and produce the results. While this process is running you can query the dynamic view

V$IO_CALIBRATION_STATUS.

The final results can be obtained from the table DBA_RSRC_IO_CALIBRATE which maintains information about the last run.

In linux/unix you can use tools like NMON to observe the I/O distribution.

Running the following query before and after IO_CALIBRATE will provide an idea of the I/O performed against individual data files.

SELECT file_no, small_read_megabytes, small_read_reqs, large_read_megabytes,large_read_reqs

FROM v$iostat_file;

Note: There are several bugs published for different versions by Oracle Support in relation to DBMS_RESOURCE_MANAGER.CALIBRATE_IO. Please take a quick look to see if your version has any bugs.

In summary, DBMS_RESOURCE_MANAGER.CALIBRATE_IO is very useful in identifying and rectifying I/O limitations.

About the author

Hari Muthuswamy, Chief Technology Officer

As a 20-Year Veteran in IT and Oracle technologies, he provides technical leadership to the organization and technical assistance during project implementations. Hari is calming force for Eagle bringing many years of superior results. Prior to his career with Eagle, Hari worked with DCC Services for over 9 years as Developer, Portal and Application Server Admin, DBA, instructor, and Technical Director. Hari’s time with DCC Services bolstered him with knowledge about technology, successful implementations of complex projects, and how to identify and groom good talent.

0 replies

Leave a Reply

Want to join the discussion?
Feel free to contribute!

Leave a Reply

Your email address will not be published. Required fields are marked *