1 Resource Manager: I/O Calibration

The new 11g feature Resource manager can let us simulate the IO load on the system by using DBMS_RESOURCE_MANAGER.CALIBRATE_IO package .

The CALIBRATE_IO simulate the actual oracle kernel operation.

In the oracle document , we need to set the ASYNC IO in order to perform the testing.

SHOW PARAMETER FILESYSTEMIO_OPTIONS

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  = 'Data File'

However, I am able to continue the testing without error.

 

 

SET SERVEROUTPUT ON

Declare

v_max_iops PLS_INTEGER:=1;

v_max_mbps PLS_INTEGER:=1;

v_actual_latency PLS_INTEGER:=1;

begin

DBMS_RESOURCE_MANAGER.CALIBRATE_IO(

max_iops=>v_max_iops,

max_mbps=>v_max_mbps,

actual_latency=>v_actual_latency);

dbms_output.put_line('Results follow: ');

dbms_output.put_line('Max IOPS: '||v_max_iops);

dbms_output.put_line('Max MBPS: '||v_max_mbps);

dbms_output.put_line('Actual Latency: '||v_actual_latency);

end;

/

Here is the screenshot without turn on the ASYNC IO,

I did  another test again and turn on the ASYNC IO.

ALTER SYSTEM SET FILESYSTEMIO_OPTIONS=SETALL SCOPE=SPFILE;

SHUTDOWN IMMEDIATE;

STARTUP;

I got better IOPS after turn on ASYNC IO.

Only one IO testing can be executed at a time. While testing is ongoing, we can query the v$io_calibration_status for the status.

 

Select status from v$io_calibration_status;

After the testing is completed, the status column change to READY.

DBA_RSRC_IO_CALIBRATE

The DBA_RSRC_IO_CALIBRATE table stores the result for the testing. The table would be purge after the database bounce.

 

select * from DBA_RSRC_IO_CALIBRATE

 

Reference

http://docs.oracle.com/cd/E11882_01/appdev.112/e10577/d_resmgr.htm