Thursday, May 10, 2012

Oracle Database 11g I/O Calibration

Every database reads and writes data on disk. This generates I/O. The front-end applications that spend most of the CPU time waiting for the read/write operations are called I/O-bound

When designing I/O system, following things are normally kept in mind

1. Storage in place and minimum requirement  
2. Application operation availability i.e. 24/7 or business hours
3. Data throughput and response times

The I/O calibration analyzes  the performance of the storage system by determining if I/O performance problems are because of the database itself or the storage subsystem. This is done by making random I/O calls to the datafiles producing results that closely matches the actual performance of the database.

Prerequisite for I/O calibration:

1. SYSDBA user
2. time_statistics = TRUE



SQL> show parameter timed_statistics


NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
timed_statistics                     boolean     TRUE


If the value of the timed_statistics parameter is false, set it to true with the following statement.


SQL> ALTER SYSTEM SET timed_statistics=true SCOPE=BOTH;
System altered.



3. Asynchronous I/O must be enabled

SQL> show parameter filesystemio_options


NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
filesystemio_options                 string      none

If the value is none, change it to setall

SQL> ALTER SYSTEM SET filesystemio_options=setall SCOPE=SPFILE;
System altered.


Bounce the instance and run the below script to ensure Asynchroinous I/O is enabled 


SQL>SELECT NAME,ASYNCH_IO FROM V$DATAFILE F,V$IOSTAT_FILE I WHERE   F.FILE#=I.FILE_NO
AND FILETYPE_NAME='DATA FILE';


NAME                                               ASYNCH_IO
-------------------------------------------------- ---------
/u03/oradata/testdb/datafiles/system01.dbf       ASYNC_ON
/u03/oradata/testdb/datafiles/sysaux01.dbf       ASYNC_ON
/u03/oradata/testdb/datafiles/undotbs01.dbf     ASYNC_ON
/u03/oradata/testdb/datafiles/ sers01.dbf         ASYNC_ON


Note: only one calibration can be performed on a database instance at a time.

Run the I/O calibration:


Use DBMS_RESOURCE_MANAGER.CALIBRATE_IO procedure to run I/O calibration. Due to the overhead from running the I/O workload, I/O calibration should only be performed when the database is idle, or during off-peak hours, to minimize the impact of the I/O workload on the normal database workload.

CONN / AS SYSDBA
SET SERVEROUTPUT ON
DECLARE
  l_latency  PLS_INTEGER;
  l_iops     PLS_INTEGER;
  l_mbps     PLS_INTEGER;
BEGIN
   DBMS_RESOURCE_MANAGER.calibrate_io (num_physical_disks => 2,
                                       max_latency        => 10,
                                       max_iops           =>  l_iops,
                                       max_mbps           => l_mbps,
                                       actual_latency     => l_latency);
  dbms_output.put_line  ('max_iops = ' || iops);
  dbms_output.put_line  ('latency  = ' || lat);
  dbms_output.put_line('max_mbps  = ' || mbps);
END;
/
Max IOPS = 40
Max MBPS = 50
Latency  = 24
PL/SQL procedure successfully completed.


While the procedure is being executed, the progress can be viewed by the below SQL from another session

SQL>select * from V$IO_CALIBRATION_STATUS;
STATUS
---------------
IN PROGRESS



This procedure issues an I/O intensive read-only workload (made up of one megabytes of random of I/Os) to the database files to determine the maximum IOPS (I/O requests per second) and MBPS (megabytes of I/O per second) that can be sustained by the storage subsystem


Viewing the Results:

The below script can be executed to see the output of I/O calibration procedure

SQL> SELECT TO_CHAR(start_time, 'DD-MON-YYY HH24:MI:SS') AS start_time,
         TO_CHAR(end_time, 'DD-MON-YYY HH24:MI:SS') AS end_time, max_iops, max_mbps,     max_pmbps,latency,
          num_physical_disks AS disks
           FROM   dba_rsrc_io_calibrate;



START_TIME           END_TIME          MAX_IOPS   MAX_MBPS  MAX_PMBPS   LATENCY   DISKS
-------------------- -------------------- ---------- ---------- ---------- -------------------
10-MAY-012 15:55:19  10-MAY-012 16:02:20     40       50      38                     24           1







No comments:

Post a Comment