需要测试新老机器的IO性能差距，可以使用DBMS_RESOURCE_MANAGER.calibrate_io。原来在exadata上专用的，后来释放到所有的database。跑的时候可以同时执行iostat -x 1 300看看，综合评估IO。另外这个跑起来要小心，不要在生产上跑，IO会跑满，utl%会100%。

1. 首先检查异步IO是否打开

SELECT d.name,
       i.asynch_io
FROM   v$datafile d,
       v$iostat_file i
WHERE  d.file# = i.file_no
AND    i.filetype_name  = 'Data File';

NAME                                               ASYNCH_IO
-------------------------------------------------- ---------
/u01/app/oracle/oradata/DB11G/system01.dbf         ASYNC_OFF
/u01/app/oracle/oradata/DB11G/sysaux01.dbf         ASYNC_OFF
/u01/app/oracle/oradata/DB11G/undotbs01.dbf        ASYNC_OFF
/u01/app/oracle/oradata/DB11G/users01.dbf          ASYNC_OFF
/u01/app/oracle/oradata/DB11G/example01.dbf        ASYNC_OFF

5 rows selected.

2. 开启异步IO（重启数据库生效）

ALTER SYSTEM SET filesystemio_options=setall SCOPE=SPFILE;
shutdown immediate;
startup
SELECT d.name,
       i.asynch_io
FROM   v$datafile d,
       v$iostat_file i
WHERE  d.file# = i.file_no
AND    i.filetype_name  = 'Data File';

NAME                                               ASYNCH_IO
-------------------------------------------------- ---------
/u01/app/oracle/oradata/DB11G/system01.dbf         ASYNC_ON
/u01/app/oracle/oradata/DB11G/sysaux01.dbf         ASYNC_ON
/u01/app/oracle/oradata/DB11G/undotbs01.dbf        ASYNC_ON
/u01/app/oracle/oradata/DB11G/users01.dbf          ASYNC_ON
/u01/app/oracle/oradata/DB11G/example01.dbf        ASYNC_ON

5 rows selected.

3. 调用DBMS_RESOURCE_MANAGER.calibrate_io存储过程

  • 输入参数num_physical_disks 指定数据库存储所用物理磁盘数。
  • 输入参数max_latency 指定目标延迟（以毫秒为单位的数据库块大小的IO请求最大容忍的延迟，一般IO平均等待在10ms左右是良好的表现）
  • 一般运行3次取平均值，每次运行可能要十几分钟，运行情况可查看V$IO_CALIBRATION_STATUS视图
SET SERVEROUTPUT ON
DECLARE
 l_latency PLS_INTEGER;
 l_iops   PLS_INTEGER;
 l_mbps   PLS_INTEGER;
BEGIN
    DBMS_RESOURCE_MANAGER.calibrate_io (num_physical_disks => 1,
                    max_latency    => 20,
                    max_iops      => l_iops,
                    max_mbps      => l_mbps,
                    actual_latency   => l_latency);
 DBMS_OUTPUT.put_line('Max IOPS = ' || l_iops);
 DBMS_OUTPUT.put_line('Max MBPS = ' || l_mbps);
 DBMS_OUTPUT.put_line('Latency = ' || l_latency);
END;
/

也可从视图查看结果

SET LINESIZE 100
COLUMN start_time FORMAT A20
COLUMN end_time FORMAT A20

SELECT TO_CHAR(start_time, 'DD-MON-YYY HH24:MI:SS') AS start_time,
   TO_CHAR(end_time, 'DD-MON-YYY HH24:MI:SS') AS end_time,
   max_iops,
   max_mbps,
   max_pmbps,
   latency,
   num_physical_disks AS disks
FROM  dba_rsrc_io_calibrate;

START_TIME           END_TIME               MAX_IOPS   MAX_MBPS  MAX_PMBPS    LATENCY      DISKS
-------------------- -------------------- ---------- ---------- ---------- ---------- ----------
25-SEP-017 11:00:21  25-SEP-017 11:11:51       51690        745        735          2          1

4. 结果含义

  • MAX_IOPS：最大IOPS
Maximum number of data block read requests that can be sustained per second
  • MAX_MBPS：最大MBPS（每秒最高读请求的最大MB值）
Maximum megabytes per second of maximum-sized read requests that can be sustained
  • LATENCY：IO延迟(ms)
Latency for data block read requests
  • MAX_PMBPS：最大PMBPS（单个进程每秒最大IO请求MB值）
Maximum megabytes per second of large I/O requests that can be sustained by a single process

参考

DBA_RSRC_IO_CALIBRATE

ORACLE-BASE - Measuring Storage Performance For Oracle Systems