11g 以前我可以透過orion的工具來測試,I/O子系統的效能,11g之後可以直接使用DBMS_RESOURCE_MANAGER.CALIBRATE_IO測試IO效能，方便了很多。

呼叫前需要滿足的條件

1 擁有SYSDBA許可權

2 timed_statistics 引數是true

3 非同步I/O是開啟狀態

檢測資料檔案是否開啟非同步的sql

COL NAME FORMAT A50

SELECT NAME,ASYNCH_IO FROM V$DATAFILE F,V$IOSTAT_FILE I

WHERE F.FILE#=I.FILE_NO

AND FILETYPE_NAME='DATA FILE';

測試注意事項

1 確定測試例項有較小的I/O請求

2 如果是RAC環境確保所有例項是OPEN狀態

-- Initiate an I/O calibration

--

 -- Input arguments:

 --  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

--

 -- Output arguments:

 --  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.

--

  PROCEDURE 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

);

num_physical_disks用以指定資料庫儲存所用物理磁碟的近似數；

max_latency指定了客戶所能容忍的最大IO 延遲時間，單位為ms;一般我們認為IO平均等待在10ms左右是一種較好的表現

SQL> set serveroutput on;

SQL> DECLARE

2 lat INTEGER;

3 iops INTEGER;

4 mbps INTEGER;

5 BEGIN

6 -- DBMS_RESOURCE_MANAGER.CALIBRATE_IO (disk_count,max_latency , iops, mbps, lat);

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 = 754

latency = 10

max_mbps = 67

PL/SQL procedure successfully completed.

可以透過DBA_RSRC_IO_CALIBRATE查詢歷史資訊

SQL> select * from DBA_RSRC_IO_CALIBRATE;

START_TIME

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

END_TIME

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

MAX_IOPS MAX_MBPS MAX_PMBPS LATENCY NUM_PHYSICAL_DISKS

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

09-APR-14 05.11.49.698767 PM

09-APR-14 05.15.23.261156 PM

754 67 81 15 2

iostat 監控資訊