Test oracle db iops

Today, i need to test one database's iops and do something for oracle db's io test.

How to test the db's iops?

It can use oracle's pl/sql package taht is dbms_resource_manager.calibrate_io.

Here is the introduction of that procedure.

 

CALIBRATE_IO Procedure

 

This procedure calibrates the I/O capabilities of storage. Calibration status is available from the V$IO_CALIBRATION_STATUS view and results for a successful calibration run are located in DBA_RSRC_IO_CALIBRATE table.

    DBMS_RESOURCE_MANAGER.CALIBRATE_IO (

   num_physical_disks      IN  PLS_INTEGER DEFAULT 1,

   max_latency             IN  PLS_INTEGER DEFAULT 20,

   max_iops                OUT PLS_INTEGER,

   max_mbps                OUT PLS_INTEGER,

   actual_latency          OUT PLS_INTEGER); 
  

 

Parameter	Description
num_physical_disks	Approximate number of physical disks in the database storage
max_latency	Maximum tolerable latency in milliseconds for database-block-sized IO requests
max_iops	Maximum number of I/O requests per second that can be sustained. The I/O requests are randomly-distributed, database-block-sized reads.
max_mbps	Maximum throughput of I/O that can be sustained, expressed in megabytes per second. The I/O requests are randomly-distributed, 1 megabyte reads.
actual_latency	Average latency of database-block-sized I/O requests at max_iops rate, expressed in milliseconds

 

 

Only users with sysdba can run this procedure to test the ions,  only one calibrate_io procedure running at a time and it will be simultaneously generate record on all node in real application cluster, for example

 

    sys@QDATA>DECLARE

  2    lat  INTEGER;

  3    iops INTEGER;

  4    mbps INTEGER;

  5  BEGIN

  6

  7     DBMS_RESOURCE_MANAGER.CALIBRATE_IO (2, 10, iops, mbps, lat);

  8

  9    DBMS_OUTPUT.PUT_LINE ('max_iops = ' || iops);

 10    DBMS_OUTPUT.PUT_LINE ('latency  = ' || lat);

 11    dbms_output.put_line('max_mbps = ' || mbps);

 12  end;

 13  /



max_iops = 71801

latency  = 1

max_mbps = 1134



PL/SQL procedure successfully completed.


  

 

 

Views for I/O calibration results

    SQL> desc V$IO_CALIBRATION_STATUS

  Name                                      Null?    Type

  ----------------------------------------- -------- ----------------------------

  STATUS                                             VARCHAR2(13)

  CALIBRATION_TIME                                   TIMESTAMP(3)

 

SQL> desc gv$io_calibration_status

  Name                                      Null?    Type

  ----------------------------------------- -------- ----------------------------

  INST_ID                                            NUMBER

  STATUS                                             VARCHAR2(13)

  CALIBRATION_TIME                                   TIMESTAMP(3)

 

Column explanation:

-------------------

STATUS:

  IN PROGRESS   : Calibration in Progress (Results from previous calibration

                  run displayed, if available)

  READY         : Results ready and available from earlier run

  NOT AVAILABLE : Calibration results not available.

 

CALIBRATION_TIME: End time of the last calibration run


  

DBA table that stores I/O Calibration results

    SQL> desc DBA_RSRC_IO_CALIBRATE

  Name                                      Null?    Type

  ----------------------------------------- -------- ----------------------------

  START_TIME                                         TIMESTAMP(6)

  END_TIME                                           TIMESTAMP(6)

  MAX_IOPS                                           NUMBER

  MAX_MBPS                                           NUMBER

  MAX_PMBPS                                          NUMBER

  LATENCY                                            NUMBER

  NUM_PHYSICAL_DISKS                                 NUMBER
  

 

 

Test oracle db iops