当前位置:数据库 > Oracle >>

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
 
CopyRight © 2022 站长资源库 编程知识问答 zzzyk.com All Rights Reserved
部分文章来自网络,