I/O Calibration in Oracle 11g


Very cool feature. For those who knew or used ORION probably it’s no big surprise, but still it’s very usefull.

You need to

1) alter one parameter: SQL>alter system set filesystemio_options=ASYNCH scope=spfile;  (needs a DB bounce)
2) Check another parameter: SQL> show parameter disk_asynch_io (if it’s TRUE, it’s OK)
3) Decide on the latency (in miliseconds) you will tolerate has the maximum for your storage system
4) Know how many physical disks you have
5) If using Linux and if  “cat /proc/sys/fs/aio-nr” is too close to cat /proc/sys/fs/aio-max-nr” edit (as root) the “/etc/sysctl.conf” file and increase the max parameter:

# vi/etc/sysctl.conf

add the following line: fs.aio-max-nr = 131072

This should be suficient for most systems, but if the value given by cat /proc/sys/fs/aio-nr” is too close to 131072, then you can increase the value of “fs.aio-max-nr” in “/etc/sysctl.conf”  file.

After this you can calibrate your I/O. You will need to do this in order to have realistic metric thresholds for you I/O.

To calibrate you have two ways:

* Calibrate with Enterprise Manager (EM)  interface
* Calibrate using the PL/SQL API

Calibrating your I/O with EM

This is supposed to be easy, but after you submit your calibration process you might wonder where it is. It’s the EM way! To get lost, or loose your context! Here are the steps to submit the process:

1) Go to Performance tab, and underneath the “Average Active Sessions” graphic click the “I/O” tab
2) After this you will need to page down again, and find that the I/O tab has a “I/O Calibration” button. Press it.
3) Now you just have to fill two text boxes: number of disks, and expected latency. Click Submit. You’re done.
4) Now go back and click the Server tab, and under “Oracle Scheduler” click “Jobs”, and then click the “Running” tab. You’ll find your Calibration Job here.

Calibrating your I/O with the PL/SQL API

Inside the DBMS_RESOURCE_MANAGER package there’s a procedure called CALIBRATE_IO that you must invoke with two variables going in:

SQL> desc DBMS_RESOURCE_MANAGER
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

Checking the Status and AfterMath of Calibration

After you submit your Calibration process either by EM or PL/SQL you can keep up to date of its status by querying the following view:

SQL> select * from v$io_calibration_status;

STATUS
-------------
CALIBRATION_TIME
---------------------------------------------------------------------------
IN PROGRESS

After a while the same query will give back a finishing status:

SQL> select * from v$io_calibration_status;

STATUS
-------------
CALIBRATION_TIME
---------------------------------------------------------------------------
READY
23-MAY-09 05.57.33.317 PM

And now it’s the time to check on the results, either by clicking again on the “I/O Calibration” button on EM and checking the “Existing Calibration Results” or by issuing the following query:

SQL> select * from dba_rsrc_io_calibrate

START_TIME
---------------------------------------------------------------------------
END_TIME
---------------------------------------------------------------------------
 MAX_IOPS   MAX_MBPS  MAX_PMBPS    LATENCY NUM_PHYSICAL_DISKS
---------- ---------- ---------- ---------- ------------------
23-MAY-09 05.37.25.117503 PM
23-MAY-09 05.57.33.316982 PM
 85         14         15         89                  1
About these ads