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.