Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
Database Tools
SQL Scripts & Samples
» Database Forum
» Slideshows
» Sitemap
Free Newsletters:
News Via RSS Feed

follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted June 13, 2016

Oracle's Calibrate_io Procedure: Why You Should Use It

By David Fitzjarrell

With the introduction of Auto Degree Of Parallelism (Auto DOP) in version 11 Oracle made it necessary to run a DBMS_RESOURCE_MANAGER procedure named CALIBRATE_IO. Oracle did not say much, really, about the procedure except that "This procedure calibrates the I/O capabilities of storage." Looking a bit deeper into the documentation reveals this: Running IO Calibration.

But that description could still be confusing. Do you need to run CALIBRATE_IO? Yes, but NOT to "calibrate the I/O capabilities of storage." Let's dig a bit further and see why you should run it.

A typical run of CALIBRATE_IO looks like this:

SQL> @calibrate_io
Enter value for no_of_disks: 1
old   7:    DBMS_RESOURCE_MANAGER.CALIBRATE_IO (&no_of_disks, 10, iops, mbps, lat);
new   7:    DBMS_RESOURCE_MANAGER.CALIBRATE_IO (1, 10, iops, mbps, lat);
max_iops = 36439
latency  = 0
max_mbps = 486

PL/SQL procedure successfully completed.


At first glance nothing seems amiss, but there are a few things you need to know about the I/O load CALIBRATE_IO generates:

All of the I/O is generated with asynchronous reads
No reads occur from buffers in the SGA, only buffers in the process heap
There is no way to specify a tablespace for the read activity
Two consecutive runs can, and often do, produce different results
The results can't be predicted

There are several 'flaws' in the CALIBRATE_IO procedure, starting with the random, single-block reads using asynchronous I/O. These calls are not the same as the I/O calls generated for db file sequential reads. The additional fact that CALIBRATE_IO buffers in the process heap also deviates from standard Oracle I/O behavior, which buffers in the SGA. The tablespace issue would be nice to correct but that I can live with. What isn't acceptable is the generated results are neither predictable nor repeatable, and any true I/O analysis needs to be both. A second run, in the same database using the same disk, shows this:

SQL> @calibrate_io
Enter value for no_of_disks: 1
old   7:    DBMS_RESOURCE_MANAGER.CALIBRATE_IO (&no_of_disks, 10, iops, mbps, lat);
new   7:    DBMS_RESOURCE_MANAGER.CALIBRATE_IO (1, 10, iops, mbps, lat);

max_iops = 34226
latency  = 2
max_mbps = 301

PL/SQL procedure successfully completed.


So it's not for the actual data that you need to run CALIBRATE_IO; it's for the data dictionary updates it performs to enable Auto DOP. Quoting from a previous article I wrote on parallel processing:

"A supplied package, DBMS_RESOURCE_MANAGER, provides the CALIBRATE_IO procedure that is used to generate a random read-only workload across all RAC instances on the cluster. The procedure takes five parameters, two input values (number of disks and maximum estimated disk latency), and three output variables to receive the computed values for maximum I/Os per second, the maximum megabytes per second, and the calculated latency. An anonymous PL/SQL block can be used to call the procedure and return the computed metrics, as follows:

Set serveroutput on size 1000000
Calc_lat number;
Calc_iops number;
Calc_mbps number;
Dbms_resource_manager.calibrate_io(&dsks,&maxlat, Calc_iops, Calc_mbps, Calc_lat);
Dbms_output.put_line('Max IOPS : '||Calc_iops);
Dbms_output.put_line('Max mbytes-per-sec: '||Calc_mbps);
Dbms_output.put_line('Calc. latency : '||Calc_lat);

As stated earlier, before Auto DOP is activated, the I/O system has to be calibrated.

Note: It cannot be stressed enough that I/O calibration is a resource-intensive operation and should not be run on a system experiencing heavy workloads. This is an after-hoursoperation that will take about 15 minutes or more to run.

Your patience will be rewarded when Auto DOP is functional.

A view, V$IO_CALIBRATION_STATUS, reports whether or not this calibration has been run. A database in need of calibration will show these results when V$IO_CALIBRATION_STATUS is queried:

SQL> select *
2 from v$io_calibration_status;

------------- -----------------------------------------------------------


Queries that would be executed in parallel on calibrated systems run serially on databases that have not had I/O calibration run. The execution plan returns an informative message about the Auto DOP calculations, immediately visible after query execution when autotrace is enabled:

SQL> select count(*)
      2 from dba_objects
      3 /


Execution Plan
Plan hash value: 3660875064
| Id  | Operation                             | Name        | Rows | Bytes | Cost (%CPU)| Time     |
|   0 | SELECT STATEMENT                      |             |    1 |       |    1099 (2)| 00:00:14 |
|   1 |   SORT AGGREGATE                      |             |    1 |       |            |          |
|   2 |     VIEW                              | DBA_OBJECTS |  320K|       |    1099 (2)| 00:00:14 |
|   3 |       UNION-ALL                       |             |      |       |            |          |
|*  4 |         FILTER                        |             |      |       |            |          |
|*  5 |       HASH JOIN                       |             |  320K|    21M|    1092 (2)| 00:00:14 |
|   6 |         INDEX FULL SCAN               | I_USER2     |  845 |  3380 |       5 (0)| 00:00:01 |
|*  7 |       HASH JOIN                       |             |  320K|    20M|    1085 (2)| 00:00:14 |
|   8 |         INDEX FULL SCAN               | I_USER2     |  845 | 18590 |       5 (0)| 00:00:01 |
|*  9 |    TABLE ACCESS STORAGE FULL          | OBJ$        |  320K|    13M|    1078 (2)| 00:00:13 |
|  10 |      NESTED LOOPS                     |             |    1 |    30 |       4 (0)| 00:00:01 |
|* 11 |         INDEX RANGE SCAN              | I_OBJ4      |    1 |    10 |       3 (0)| 00:00:01 |
|* 12 |         INDEX RANGE SCAN              | I_USER2     |    1 |    20 |       1 (0)| 00:00:01 |
|* 13 |      HASH JOIN                        |             |   29 |   232 |      7 (15)| 00:00:01 |
|  14 |         INDEX FULL SCAN               | I_LINK1     |   29 |   116 |       1 (0)| 00:00:01 |
|  15 |         INDEX FULL SCAN               | I_USER2     |  845 |  3380 |       5 (0)| 00:00:01 |

Predicate Information (identified by operation id):
4 - filter("O"."TYPE#"<>4 AND "O"."TYPE#"<>5 AND "O"."TYPE#"<>7 AND "O"."TYPE#"<>8 AND "O"."TYPE#"<>9 AND "O"."TYPE#"<>11 AND "O"."TYPE#"<>12 AND "O"."TYPE#"<>13 AND "O"."TYPE#"<>14 AND "O"."TYPE#"<>22 AND "O"."TYPE#"<>87 AND "O"."TYPE#"<>88 OR BITAND("U"."SPARE1",16)=0 OR ("O"."TYPE#"=4 OR "O"."TYPE#"=5 OR "O"."TYPE#"=7 OR "O"."TYPE#"=8 OR "O"."TYPE#"=9 OR "O"."TYPE#"=10 OR "O"."TYPE#"=11 OR "O"."TYPE#"=12 OR "O"."TYPE#"=13 OR "O"."TYPE#"=14 OR "O"."TYPE#"=22 OR "O"."TYPE#"=87) AND ("U"."TYPE#"<>2 AND SYS_CONTEXT('userenv','current_edition_name')='ORA$BASE' OR "U"."TYPE#"=2 AND "U"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id')) OR EXISTS (SELECT 0 FROM SYS."USER$" "U2",SYS."OBJ$" "O2" WHERE "O2"."TYPE#"=88 AND "O2"."DATAOBJ#"=:B1 AND "U2"."TYPE#"=2 AND "O2"."OWNER#"="U2"."USER#" AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id')))))
5 - access("O"."SPARE3"="U"."USER#")
7 - access("O"."OWNER#"="U"."USER#")
9 - storage("O"."LINKNAME" IS NULL AND "O"."TYPE#"<>10 AND BITAND("O"."FLAGS",128)=0 AND "O"."NAME"<>'_NEXT_OBJECT' AND "O"."NAME"<>'_default_auditing_options_')
filter("O"."LINKNAME" IS NULL AND "O"."TYPE#"<>10 AND BITAND("O"."FLAGS",128)=0 AND "O"."NAME"<>'_NEXT_OBJECT' AND "O"."NAME"<>'_default_auditing_options_')
11 - access("O2"."DATAOBJ#"=:B1 AND "O2"."TYPE#"=88)
12 - access("O2"."OWNER#"="U2"."USER#" AND "U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id')))
13 - access("L"."OWNER#"="U"."USER#")


- automatic DOP: skipped because of IO calibrate statistics are missing

       17 recursive calls
     2 db block gets
     4788 consistent gets
     4772 physical reads
        0 redo size
      528 bytes sent via SQL*Net to client
      524 bytes received via SQL*Net from client
        2 SQL*Net roundtrips to/from client
        0 sorts (memory)
        0 sorts (disk)
        1 rows processed


Running the calibration process is simple and straightforward, passing to the script the number of disks and an expected latency of 10. The procedure returns the actual latency, along with the maximum IOPs and the maximum megabytes per second the storage system can support:

SQL> @calibrate_io
Enter value for dsks: 36
Enter value for maxlat: 10
old 7: dbms_resource_manager.calibrate_io (&dsks, &maxlat, Calc_iops, Calc_mbps, Calc_lat);
new 7: dbms_resource_manager.calibrate_io (36, 10, Calc_iops, Calc_mbps, Calc_lat);
Max IOPS : 1022
Max mbytes-per-sec: 2885
Calc. latency : 11

PL/SQL procedure successfully completed.

Elapsed: 00:07:10.57


Once this calibration has completed, a query of V$IO_CALIBRATE_STATUS reports a different result than it did previously:

SQL> select *
      2 from v$io_calibration_status;

------------- -----------------------------------------------------------
READY         08-APR-13 AM


If you query V$IO_CALIBRATION_STATUS during an I/O calibration run, the view will report results similar to the following output:

SQL> select *
      2 from v$io_calibration_status;

------------- -----------------------------------------------------------


After I/O calibration has been successfully completed, if the query or statement in question meets or exceeds the serial execution time represented by the parallel_min_time_threshold parameter, then Auto DOP will set the degree of parallelism, regardless of whether any of the objects are explicitly set for parallel execution. You may find that once I/O calibration is completed, some tasks may take longer to complete because they are queued. This may occur because the parallel resources allocated may not be sufficient for all the queries and statements that qualify for parallel execution. There is a Metalink Note, document id 1393405.1, that explains how to delete the I/O calibration statistics. In summary, there is a single table, RESOURCE_IO_CALIBRATE$, that the two views are based on. Deleting the data from this table clears the I/O calibration statistics, so that Auto DOP will no longer function."

Should you run CALIBRATE_IO on your system? Most likely the answer is "yes", but it isn't run for the storage 'statistics' it gathers, it's run to enable Auto DOP and parallel queueing. There are third-party utilities which are better and much more consistent to generate usable (to the DBA) storage metrics; unfortunately these utilities won't generate the data Oracle needs to make Auto DOP functional. So it seems that CALIBRATE_IO is a 'necessary evil'; it all comes down to knowing why CALIBRATE_IO should be run. Hopefully you now have that knowledge.

See all articles by David Fitzjarrell

Oracle Archives

Comment and Contribute


(Maximum characters: 1200). You have characters left.



Latest Forum Threads
Oracle Forum
Topic By Replies Updated
Oracle Data Mining: Classification jan.hasller 0 July 5th, 07:19 AM
Find duplicates - Unique IDs Lava 5 July 2nd, 08:30 AM
no matching unique or primary key rcanter 1 April 25th, 12:32 PM
Update values of one table based on condition of values in other table using Trigger Gladiator 3 February 29th, 06:01 PM