环境为Oracle linux 6.4 +database 11.2.0.4(RAC 2节点 )+asm 11.2.0.4
对了,另外如果数据库使用的是asm,那么如果验证的死数据文件需要使用datadg中的所有物理磁盘,而不是fra中的物理磁盘。虽然我使用存储化的lun进行的映射,但是
num_physical_disks必须是真实物理磁盘个数,因为是测试是一个磁盘,那么这个参数为1。
1、验证是否启动async i/o
[oracle@rac-one ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Mon Apr 21 22:35:23 2014
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
SQL> show parameter disk_asynch_io(查看数据库是否启用了异步i/o)
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
disk_asynch_io boolean TRUE
SQL> !
[oracle@rac-one ~]$ more /proc/sys/fs/aio-max-nr (系统slots最大数量)
1048576
[oracle@rac-one ~]$ more /proc/sys/fs/aio-nr (当前使用的slots)
23680
[oracle@rac-one ~]$ exit
exit
SQL> col name for a50
SQL> set linesize 200
SQL> select name,asynch_io from v$datafile f,v$iostat_file i where f.file#=i.file_no and (filetype_name='Data File' or filetype_name='Temp File'); -------------(查看启用了async的文件信息)
NAME ASYNCH_IO
-------------------------------------------------- ---------
+DATADG/rac/datafile/system.262.839537769 ASYNC_ON
+DATADG/rac/datafile/system.262.839537769 ASYNC_ON
+DATADG/rac/datafile/sysaux.263.839537911 ASYNC_ON
+DATADG/rac/datafile/undotbs1.264.839538031 ASYNC_ON
+DATADG/rac/datafile/undotbs2.266.839538155 ASYNC_ON
+DATADG/rac/datafile/users.267.839538199 ASYNC_ON
6 rows selected.
SQL>
开始验证i/o:
SQL> set serveroutput on
SQL> declare
2 lat integer;
3 iops integer;
4 mbps integer;
5 begin
6 --dbms_resource_manager.calibrate_io(,,iops,mbps,lat);
7 dbms_resource_manager.calibrate_io(1,10,iops,mbps,lat);
8 dbms_output.put_line('max_iops=' || iops);
9 dbms_output.put_line('latency=' || lat);
10 dbms_output.put_line('max_mbps=' || mbps);
11 end;
12 /
查看状态信息:
SQL> select * from v$io_calibration_status;
STATUS CALIBRATION_TIME
------------- ---------------------------------------------------------------------------
IN PROGRESS
SQL> select file_no,small_read_megabytes,small_read_reqs,large_read_megabytes,large_read_reqs from v$iostat_file;
FILE_NO SMALL_READ_MEGABYTES SMALL_READ_REQS LARGE_READ_MEGABYTES LARGE_READ_REQS
---------- -------------------- --------------- -------------------- ---------------
0 0 24 0 0
0 107 6819 0 0
0 0 0 0 0
0 0 0 0 0
0 0 0 0 0
0 0 0 0 0
0 0 0 0 0
0 0 0 0 0
0 0 0 0 0
0 0 0 0 0
1 40 4808 884 901
FILE_NO SMALL_READ_MEGABYTES SMALL_READ_REQS LARGE_READ_MEGABYTES LARGE_READ_REQS
---------- -------------------- --------------- -------------------- ---------------
1 0 4 0 0
2 28 2515 777 779
3 5 577 351 351
4 3 404 264 264
可知正在进行测试中:
执行结束将输出结果如下:
SQL> declare
2 lat integer;
3 iops integer;
4 mbps integer;
5 begin
6 --dbms_resource_manager.calibrate_io(,,iops,mbps,lat);
7 dbms_resource_manager.calibrate_io(1,10,iops,mbps,lat);
8 dbms_output.put_line('max_iops=' || iops);
9 dbms_output.put_line('latency=' || lat);
10 dbms_output.put_line('max_mbps=' || mbps);
11 end;
12 /
max_iops=45
latency=42
max_mbps=12
PL/SQL procedure successfully completed.
SQL>
可以知道这两个节点请求小块读为45,最大吞吐量为12M/s。虚拟机就是卡的掉渣。