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 theV$IO_CALIBRATION_STATUS view and results for a successful calibration run are located inDBA_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

[sql] view plaincopyprint?

sys@QDATA>DECLARE

2 lat INTEGER;

3 iops INTEGER;

4 mbps INTEGER;

5 BEGIN

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

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