oracle测试io -m6米乐安卓版下载

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查看测试结果。

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.