oracle IO性能测试 -- calibrate_io（转载）

oracle IO性能测试 – calibrate_io

oracle linux 7中查看异步IO cat /proc/slabinfo |grep kio 没有显示，忽略。

需要测试新老机器的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$datafiled,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$datafiled,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

参考

https://docs.oracle.com/cd/B28359_01/server.111/b28320/statviews_4213.htm#REFRN23652

https://oracle-base.com/articles/misc/measuring-storage-performance-for-oracle-systems