一.背景

oracle官方提供了DBMS_RESOURCE_MANAGER.CALIBRATE_IO存储过程，用于测试当前数据库实例的I/O性能。

二.介绍

2.1.DBMS_RESOURCE_MANAGER.CALIBRATE_IO参数如下：

DBMS_RESOURCE_MANAGER.CALIBRATE_IO (
num_physical_disks      IN 	PLS_INTEGER DEFAULT 1 ,
max_latency  		IN 	PLS_INTEGER DEFAULT  20,
max_jobs  		OUT	PLS_INTEGER,
max_mbps  		OUT	PLS_INTEGER,
actual_latency  	OUT	PLS_INTEGER );

2.2.常用的输入参数具体如下

• num_physical_disks：当前系统中存储盘的个数，根据实际情况填写即可。
• max_latency：所能容忍的最大延迟(单位为ms)，对于OLTP系统，建议将该值设置在20以内。

2.3.测试之前需要确认当前环境是否开启了异步I/O，命令如下：

SQL>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
+DATA/jason/datafile/system01. dbf ASYNCOFF
+DATA/jason/datafile/sysaux01. dbf ASYNCOFF
+DATA/jason/datafile/undotbs01. dbf ASYNCOFF
+DATA/jason/datafile/users01. dbf ASYNCOFF

--AYNC_OFF表示未开启，开启异步I/O(需要重启数据库才能生效)

SQL> ALTER SYSTEM SET   filesystemio_options=setall SCOPE=SPFILE; 
SQL>set  serveroutput  on;
SQL>DECLARE
		lat INTEGER;
		iops INTEGER;
		mbps INTEGER;
	BEGIN
		DBMS_RESOURCE_MANAGER.CALIBRATE_IO(1, 20, iops, mbps, lat);
		DBMS_OUTPUT.PUT_LINE('max _ iops=' || /iops);
		DBMS_OUTPUT.PUT_LINE('latency=' || 1at);
		dbms_output.put line('max _ mbps=' || mbps);
	end;

2.4.

输出结果具体如下：

• Max IOPS=51800 --表示每秒可以维持的最大I/O请求数。
• Max MBPS = 654 --表示可以维持的最大I/O吞吐量
• Latency = 8 --actual_latency:以max_iops表示当前I/O请求的平均延迟，单位为ms。
  同样，我么也可以通过视图v$io_calibration_status查看测试结果。

三.以下是我在墨天轮21c实训环境测试的过程：

SQL> set line 200
SQL> col name for a120
SQL> 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';  2    3    4  
NAME															 ASYNCH_IO
------------------------------------------------------------------------------------------------------------------------ ---------
/data/app/oracle/oradata/orcl/system01.dbf										 ASYNC_OFF
/data/app/oracle/oradata/orcl/sysaux01.dbf										 ASYNC_OFF
/data/app/oracle/oradata/orcl/undotbs01.dbf										 ASYNC_OFF
/data/app/oracle/oradata/orcl/users01.dbf										 ASYNC_OFF
/data/app/oracle/oradata/orcl/example01.dbf										 ASYNC_OFF
SQL> 
ALTER SYSTEM SET filesystemio_options=setall SCOPE=SPFILE; 
SQL> ALTER SYSTEM SET filesystemio_options=setall SCOPE=SPFILE; 
System altered.
SQL> shutdown abort;
ORACLE instance shut down.
SQL> startup;
ORACLE instance started.
Total System Global Area 1586708480 bytes
Fixed Size		    2253624 bytes
Variable Size		  989859016 bytes
Database Buffers	  587202560 bytes
Redo Buffers		    7393280 bytes
Database mounted.
Database opened.
SQL> SQL> SQL> set line 200
SQL> col name for a120
SQL> 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';  2    3    4  

NAME															 ASYNCH_IO
------------------------------------------------------------------------------------------------------------------------ ---------
/data/app/oracle/oradata/orcl/system01.dbf										 ASYNC_ON
/data/app/oracle/oradata/orcl/sysaux01.dbf										 ASYNC_ON
/data/app/oracle/oradata/orcl/undotbs01.dbf										 ASYNC_ON
/data/app/oracle/oradata/orcl/users01.dbf										 ASYNC_ON
/data/app/oracle/oradata/orcl/example01.dbf										 ASYNC_ON
SQL> 
set serveroutput on;
DECLARE
	lat INTEGER;
	iops INTEGER;
	mbps INTEGER;
BEGIN
	DBMS_RESOURCE_MANAGER.CALIBRATE_IO(1, 20, iops, mbps, lat);
	DBMS_OUTPUT.PUT_LINE('max_iops = ' || iops);
	DBMS_OUTPUT.PUT_LINE('latency=  ' || lat);
	dbms_output.put_line('max_mbps = ' || mbps);
end;
/
SQL> set serveroutput on;
DECLARE
        lat INTEGER;
        iops INTEGER;
        mbps INTEGER;
BEGIN
        DBMS_RESOURCE_MANAGER.CALIBRATE_IO(1, 20, iops, mbps, lat);
        DBMS_OUTPUT.PUT_LINE('max_iops = ' || iops);
        DBMS_OUTPUT.PUT_LINE('latency=  ' || lat);
        dbms_output.put_line('max_mbps = ' || mbps);
end;
/SQL>   2    3    4    5    6    7    8    9   10   11  

max_iops = 4993
latency  = 18.614
max_mbps = 146

Note: The high I/O latencies from the calibration run indicate that the
calibration I/Os are being serviced mostly from disk. If your storage has a
cache, you may achieve better results by rerunning. Rerunning may benefit from
the storage cache.
max_iops = 4993
latency=  19
max_mbps = 146
PL/SQL procedure successfully completed.