Tuesday, March 5, 2013

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'

image

image

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,

image

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

ALTER SYSTEM SET FILESYSTEMIO_OPTIONS=SETALL SCOPE=SPFILE;
SHUTDOWN IMMEDIATE;
STARTUP;

image

I got better IOPS after turn on ASYNC IO.

image

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;
image

image

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

image

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

image

 

Reference

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

1 comments:

  1. We put forth a comprehensive range of Commercial Weighing Scales that includes Mini .... Creating a niche of Calibration Precision Weights such as Calibration losangelescalibration.com

    ReplyDelete

 

SQL Panda Copyright © 2011 - |- Template created by O Pregador - |- Powered by Blogger Templates