IT Solutions Technology Blog

   The DBA can assess the I/O capability of the database’s storage system by using the PL/SQL function DBMS_RESOURCE_MANAGER.CALIBRATE_IO() – this routine issues a very I/O intensive read-only workload to thedatabase’s files to assess the maximum IOPS (I/O requests per second) and MBPS (megabytes of I/O per second) that canbesustained. This data can be reexamined at any time using the DBA table, DBA_RSRC_IO_CALIBRATE, that stores I/Ocalibration results. The calibration should be performed when the database is idle and during off-peak hours to minimizethe heavy I/O workload from interfering with the regular workload and vice versa. I/O calibration can be used tounderstand the performance of the storage subsystem and figure out whether I/O performance problems stem from thedatabase or the storage subsystem. Unlike various external I/O calibration tools, this tool uses the Oracle code stack and issues I/O randomly rather than sequentially the results therefore much more closely match the actual database Performance

calibrate_io.sql

rem ***********************************************************
rem
rem
File: calibrate_io.sql
rem
Description: PL/SQL reoutine to calibrate IO
rem  
rem
From ‚Oracle Performance Survival Guide‘ by Guy Harrison
rem
Chapter 21 Page 624
rem
ISBN: 978-0137011957
rem
See www.guyharrison.net for further information
rem 
rem
This work is in the public domain NSA
rem  
rem
rem *********************************************************

set serveroutput on
set echo on

DECLARE
   v_max_iops         NUMBER;
   v_max_mbps         NUMBER;
   v_actual_latency   NUMBER;
BEGIN
   DBMS_RESOURCE_MANAGER.calibrate_io(
        num_physical_disks => 8,
        max_latency => 10,
        max_iops => v_max_iops,
        max_mbps => v_max_mbps,
        actual_latency => v_actual_latency);
       
   DBMS_OUTPUT.put_line(‚Max IOPS=‘ || v_max_iops);
   DBMS_OUTPUT.put_line(‚Max MBps=‘ || v_max_mbps);
   DBMS_OUTPUT.put_line(‚Latency =‘ || v_actual_latency);
  
END;
/