一.背景

oracle官方提供了DBMS_RESOURCE_MANAGER.CALIBRATE_IO存儲(chǔ)過程，用于測(cè)試當(dāng)前數(shù)據(jù)庫實(shí)例的I/O性能。

二.介紹

2.1.DBMS_RESOURCE_MANAGER.CALIBRATE_IO參數(shù)如下：

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.常用的輸入?yún)?shù)具體如下

• num_physical_disks：當(dāng)前系統(tǒng)中存儲(chǔ)盤的個(gè)數(shù)，根據(jù)實(shí)際情況填寫即可。
• max_latency：所能容忍的最大延遲(單位為ms)，對(duì)于OLTP系統(tǒng)，建議將該值設(shè)置在20以內(nèi)。

2.3.測(cè)試之前需要確認(rèn)當(dāng)前環(huán)境是否開啟了異步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(需要重啟數(shù)據(jù)庫才能生效)

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.

輸出結(jié)果具體如下：

• Max IOPS=51800 --表示每秒可以維持的最大I/O請(qǐng)求數(shù)。
• Max MBPS = 654 --表示可以維持的最大I/O吞吐量
• Latency = 8 --actual_latency:以max_iops表示當(dāng)前I/O請(qǐng)求的平均延遲，單位為ms。
  同樣，我么也可以通過視圖v$io_calibration_status查看測(cè)試結(jié)果。

三.以下是我在墨天輪21c實(shí)訓(xùn)環(huán)境測(cè)試的過程：

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.