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
