Test oracle db iops
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
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