Saturday, August 09, 2008

Resource Manager I/O Calibration in 11g

You are considering a new platform for the Oracle database; or perhaps worried about the I/O capabilities of the existing platform. How do you know if the I/O drivers, LUN creation, etc. are capableof handling the I/O load? This is a very important question; I/O is the single most important thing in an Oracle database that can cripple an application, however large or small.

Here comes the Calibrate I/O tool in Oracle Database 11g. There are some really cool features of 11g that never made into the blitzkrieg of Oracle Marketing. This is one of them. This is a procedure in the DBMS_RESOURCE_MANAGER package. It drives some I/O to the database files to test the throughput and much more metrics.

So, what is the big deal? This type of tests can be done by simple unix system as well; or even by little apps from the storage vendors.

The important difference is that the tool uses Oracle database software libraries to drive the I/O; not some generic I/O generator. This capability is important - you are testing the I/O for an Oracle database, right?

So, let's get on with the how-it-works part of the post. You need to have asynch I/O capability otherwise this will fail with the following message:


ERROR at line 1:ORA-56708: Could not find any datafiles with asynchronous i/o capability

ORA-06512: at "SYS.DBMS_RMIN", line 453ORA-06512: at "SYS.DBMS_RESOURCE_MANAGER", line 1153ORA-06512: at line 7

To enable asynch I/O, set two values in the init.ora file (or spfile) and recycle the instances:

disk_asynch_io = true

filesystemio_options = asynch

The first one is default; so you may not have to change it. The default of the second one is NONE; so you will probably have to change it. After setting these values and recycling the instance, confirm at the file level:

select name,asynch_io from v$datafile f,v$iostat_file i 

where f.file#=i.file_no 

and (filetype_name='Data File' or filetype_name='Temp File')



-------------------------------------------------- ---------
+DG1/labrac/datafile/system.261.648437429 ASYNC_ON

+DG1/labrac/datafile/system.261.648437429 ASYNC_ON

+DG1/labrac/datafile/sysaux.262.648437447 ASYNC_ON

+DG1/labrac/datafile/undotbs1.263.648437455 ASYNC_ON

+DG1/labrac/datafile/undotbs2.265.648437477 ASYNC_ON

+DG1/labrac/datafile/users.266.648437523 ASYNC_ON

+DG1/sras_01 ASYNC_ON

This shows the files have Asynch I/O enabled.

Caution: even if the above query returns ASYNC_ON, you may not be able to run the calibrate process. The culprit is the number of asynch I/O slots available in the system. If there are no free slots, the files will not use asynch I/O and you will get ORA-56708. To find out the maximum avalable asynch I/O slots:

$ cat /proc/sys/fs/aio-max-nr

To find out how many are being used:

$ cat /proc/sys/fs/aio-nr

Well, that sort of tells it all. Of the 65536, all are being used; so we are getting ORA-56708. The reason was I was runninganother database on the server, which was consuming all those slots. After shutting down the other database, I checked the utilization again:

$ cat /proc/sys/fs/aio-nr


The value dropped substantially and allowed my calibration to proceed.

Now start the calibration process. The precedure CALIBRATE_IO of the package DBMS_RESOURCE_MANAGER accepts two parameters:

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

And it has three OUT parameters:

  • 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

Since this has OUT parameters, we will have to write a little PL/SQL program to get the values.

set serveroutput on


l_latency integer;

l_iops integer;

l_mbps integer;


dbms_resource_manager.calibrate_io (

18, /* # of disks */

10, /* max latency */

l_iops, /* I/O Ops/sec */

l_mbps, /* MBytes/sec */

l_latency /* actual latency */


dbms_output.put_line ('I/O Ops/sec = ' l_iops);

dbms_output.put_line ('Actual Latency = ' l_latency);

dbms_output.put_line('MB/sec = ' l_mbps);



When this is going on, you can check the status:

SQL> select * from v$io_calibration_status

2 /


This shows that the operation is still going on. After some time you will see the status change to READY. Here is the output from my system (a RAC database with 2 nodes, each with 4 CPUs, with a Dell NAS, with 28 disks).

I/O Ops/sec = 122
Actual Latency = 15
MB/sec = 13

PL/SQL procedure successfully completed.

So, it shows us that the actual latency is somewhat like 15 milliseconds. The system can sustain a maximum of 122 I/O Ops per second with a throuput of 13 MB/sec.

You can check the results of the I/O Calibration run later from the data dictionary view DBA_RSRC_IO_CALIBRATE. Let's check this view. Since it has only one row and there are many columns, I used the very handy Print Table utility by Tom Kyte.

SQL> exec print.tbl('select * from dba_rsrc_io_calibrate');

MAX_IOPS : 122

The columns are self explanatory, in line with the previous output, except the MAX_PMBPS, which shows the maximum sustainable I/O in MB/s for a single process.

This output is great; but it is an aggregation. You can check another view V$IOSTAT_FILE for file-level metrics in calibration run:

select file_no,small_read_megabytes,small_read_reqs,


from v$iostat_file


---------- -------------------- --------------- -------------------- ---------------
1 267 33234 230 256 1 0 6 0 2
2 514 50337 318 331
3 121 15496 118 118
4 44 5661 43 43
5 1 83 0 0 6 46 5846 45 45

In addition to the columns, this view has a lot more columns. Here is the output for a single datafile:

SQL> exec print.tbl('select * from v$iostat_file where file_no = 1')





















Using these merics you can decide if the I/O is sufficient or still needs more tweaking.

Recently I used it on a system that required Oracle 10g. But I installed 11g on that server and ran this calibration test, which unearthed some inherent issues with the I/O system. Once rectified, and reconfirmed with the calibration tool, it was ready for the 10g installation.


Anonymous said...

1. what is your OS? linux?
2. try to search the print.tbl utility from "ask tom", but failed. could you share?


Unknown said...

(1) Yes, my OS is linux. AMD 64 chip.

(2) I searched AskTom and couldn't find it on the search. But, here is one that may help:

Anonymous said...

get it. thank you!

Anonymous said...

Reason for not finding it on AskTom is because the procedure is called "print_table" there.


Unknown said...

Thanks Arup -- it was useful.

Sam Humphreys said...

Gosh, I’ve been looking about this specific topic for about an hour, glad i found it in your website

Anonymous said...

Thank you for sharing Arup

But it's very important to know:

"The database where calibration'ss to be executed, needs to be quiesced or the calibration results may become "tainted"


AndrewT said...

Hi Arup, I've just come across this feature (I could really have used it while setting up a new server a few months ago, but that's life). I assume the input parameter max_latency should be the rotational latency of the disks (ie. 4mS for a 15Krpm disk). Is this the correct interpretation?
Thanks, Andrew

Anonymous said...

how did you fine out the number of disks the luns were made up from?

is it as easy as knowing how many disks are in the Enterprise storage? or did you ask someone to find out the exact number of disks used to build the luns to be used for your installation?

Unknown said...

@Anonymous - sorry; since you didn't give your name I have no way of addressing you except in this non-specific way.

No; the number of disks are not something you will get from Enteprrise Manager. LUNs are created from physical disks; so you should ask whoever created the LUNs, typically the SAN administrator.

Unknown said...

@Andree T - very sorry for the delayed response. I just saw this post. Your question is a good one. Yes, it's the maximum rated latency which is typically that the manufacturer spcifies.

Anonymous said...

What would be expceted calibration for the following configuration.
18x300GB 15K RPM Disks
Direct Attachd Storage
Configured as 3 RAID-5 Groups with 6 disks each group
3 gbps adapters

Unknown said...

@Anonymous on April 5th: Calibration is highly dependent to your specific machine. It's not possible or necessary to speculate. That was the the job of the calibration tool.

calibration said...

Hi there, awesome site. I thought the topics you posted on were very interesting. I tried to add your RSS to my feed reader and it a few. take a look at it, hopefully I can add you and follow...

Temperature calibration

sonu said...

Hi Anup,

Needless to say one more good post.

for I/O calibration I have one question about input parameter - MAX_LATENCY. I am not sure what value I can defined? I have an idea about NUM_DISKS used in my database.

Is this number just for informational purposes or is it actually used? If the the later, then what should it be set to ?

jacklinemelda said...

Rather than struggling with browsing the Internet, endless visits to the library or tagging along with your instructor to ask questions about your assignment, you can easily Already Written Essay Services or request for Custom Essay Writing Services from us.

Chris Lucas said...

One of the main reason students opt for online assignment help is the time delivery of the given tasks. Varied tasks at the universities make it a hassle for the students to devote sufficient time to each individual task. As such they either fail to submit the given assignment task within the deadlines or complete the task haphazardly getting scores below the passing marks. If you are stuck with your research projects, essays, case studies, business reports or need Urgent Assignment Help, then myassignmenthelp is the place to be

Unknown said...

I can't believe I can earn money weekly from trading , this is amazing , and all this is from the effort of a company called skylink technology whom I met online and help me out in trading and gave me good tips about trading physiology... indeed skylink technology is a bitcoin/binary forex experts and company and I won't stop thanking them and sharing my testimony until am fully satisfied...... Interested traders should  free free to contact mail:  or  whatsapp/telegram: +1(213)785-1553