VMCD.ORG » 11G新特性 IO Calibration 评测

11G新特性 IO Calibration可以帮我们估算出存储的读写性能，在使用这个特性之前我们需要满足一些条件：

.在linux系统中默认是不开启异步IO的
SQL> show parameter filesystemio_options

NAME TYPE VALUE
———————————— ———– ——————————
filesystemio_options string none

可以通过以下语句查找asynchronous I/O是否被开启：

SQL> col name format a50
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’);

NAME ASYNCH_IO
————————————————– ———
/data/oracle/oradata/yhddb1/system.dbf ASYNC_OFF
/data/oracle/oradata/yhddb1/system.dbf ASYNC_OFF
/data/oracle/oradata/yhddb1/sysaux.dbf ASYNC_OFF
/data/oracle/oradata/yhddb1/sysaux.dbf ASYNC_OFF
/data/oracle/oradata/yhddb1/undotbs01.dbf ASYNC_OFF
/data/oracle/oradata/yhddb1/undotbs01.dbf ASYNC_OFF
/data/oracle/oradata/yhddb1/qipei_data01.dbf ASYNC_OFF
/data/oracle/oradata/yhddb1/lg_index01.dbf ASYNC_OFF
/data/oracle/oradata/yhddb1/qipei_index01.dbf ASYNC_OFF
/data/oracle/oradata/yhddb1/undotbs02.dbf ASYNC_OFF
/data/oracle/oradata/yhddb1/qipei_data02.dbf ASYNC_OFF
/data/oracle/oradata/yhddb1/md_data01.dbf ASYNC_OFF
/data/oracle/oradata/yhddb1/qipei_data03.dbf ASYNC_OFF
/data/oracle/oradata/yhddb1/qipei_index02.dbf ASYNC_OFF
/data/oracle/oradata/yhddb1/tms_data01.dbf ASYNC_OFF
/data/oracle/oradata/yhddb1/undotbs03.dbf ASYNC_OFF
/data/oracle/oradata/yhddb1/lg_index02.dbf ASYNC_OFF
/data/oracle/oradata/yhddb1/tms_idx01.dbf ASYNC_OFF
/data/oracle/oradata/yhddb1/tms_data02.dbf ASYNC_OFF
/data/oracle/oradata/yhddb1/pos_data_01.dbf ASYNC_OFF
/data/oracle/oradata/yhddb1/md_data02.dbf ASYNC_OFF
/data/oracle/oradata/yhddb1/lg_index03.dbf ASYNC_OFF
/data/oracle/oradata/yhddb1/pos_data_02.dbf ASYNC_OFF
/data/oracle/oradata/yhddb1/pos_index_01.dbf ASYNC_OFF
/data/oracle/oradata/yhddb1/lg_index04.dbf ASYNC_OFF
/data/oracle/oradata/yhddb1/lg_index05.dbf ASYNC_OFF
/data/oracle/oradata/yhddb1/ims_data01.dbf ASYNC_OFF
/data/oracle/oradata/yhddb1/lg_index06.dbf ASYNC_OFF
/data/oracle/oradata/yhddb1/ims_index01.dbf ASYNC_OFF
/data/oracle/oradata/yhddb1/md_data05.dbf ASYNC_OFF
/data/oracle/oradata/yhddb1/md_data03.dbf ASYNC_OFF
/data/oracle/oradata/yhddb1/tms_idx02.dbf ASYNC_OFF
/data/oracle/oradata/yhddb1/lg_index07.dbf ASYNC_OFF
/data/oracle/oradata/yhddb1/md_data04.dbf ASYNC_OFF
/data/oracle/oradata/yhddb1/ttuser01.dbf ASYNC_OFF
/data/oracle/oradata/yhddb1/tms_data03.dbf ASYNC_OFF
/data/oracle/oradata/yhddb1/ttuser02.dbf ASYNC_OFF
/data/oracle/oradata/yhddb1/lg_index08.dbf ASYNC_OFF
/data/oracle/oradata/yhddb1/lg_index09.dbf ASYNC_OFF
/data/oracle/oradata/yhddb1/lg_index10.dbf ASYNC_OFF
/data/oracle/oradata/yhddb1/lg_index11.dbf ASYNC_OFF
/data/oracle/oradata/yhddb1/lg_index12.dbf ASYNC_OFF
/data/oracle/oradata/yhddb1/lg_index13.dbf ASYNC_OFF
/data/oracle/oradata/yhddb1/ttuser03.dbf ASYNC_OFF
/data/oracle/oradata/yhddb1/tms_data04.dbf ASYNC_OFF
/data/oracle/oradata/yhddb1/tms_data05.dbf ASYNC_OFF
/data/oracle/oradata/yhddb1/ttuser04.dbf ASYNC_OFF
/data/oracle/oradata/yhddb1/ttuser05.dbf ASYNC_OFF
/data/oracle/oradata/yhddb1/ttuser06.dbf ASYNC_OFF

我们需要打开异步io
SQL> show parameter FILESYSTEMIO_OPTIONS;

NAME TYPE VALUE
———————————— ———– ——————————
filesystemio_options string SETALL

设置这个参数需要重启数据库。参数“filesystemio_options” 支持4种值：
ASYNCH: 使Oracle支持文件的异步（Asynchronous）IO；
DIRECTIO：使Oracle支持文件的Direct IO；
SETALL：使Oracle同时支持文件的Asynchronous IO和Direct IO；
NONE：使Oracle关闭对Asynchronous IO和Direct IO的支持。

Syntax

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

num_physical_disks —— Approximate number of physical disks in the database storage
max_latency —— Maximum tolerable latency in milliseconds for database-block-sized IO requests
max_iops —— Maximum number of I/O requests per second that can be sustained. The I/O requests are randomly-distributed, database-block-sized reads.
max_mbps —— Maximum throughput of I/O that can be sustained, expressed in megabytes per second. The I/O requests are randomly-distributed, 1 megabyte reads.
actual_latency —— Average latency of database-block-sized I/O requests at max_iops rate, expressed in milliseconds

我们可以通过 DBMS_RESOURCE_MANAGER.CALIBRATE_IO 测算出存储的性能 disk_count表示实际的物理磁盘个数，max_latency为最大容忍的延迟，这里我们设置为10

————————–

SQL> set serveroutput on;
SQL> DECLARE
2 lat INTEGER;
3 iops INTEGER;
4 mbps INTEGER;
5 BEGIN
6 — DBMS_RESOURCE_MANAGER.CALIBRATE_IO (disk_count,max_latency , iops, mbps, lat);
7 DBMS_RESOURCE_MANAGER.CALIBRATE_IO (2, 10, iops, mbps, lat);
8
9 DBMS_OUTPUT.PUT_LINE (‘max_iops = ‘ || iops);
10 DBMS_OUTPUT.PUT_LINE (‘latency = ‘ || lat);
11 dbms_output.put_line(‘max_mbps = ‘ || mbps);
12 end;
13 /

max_iops = 901
latency = 15
max_mbps = 800

通过以下视图可以查看 I/O calibration results

SQL> desc V$IO_CALIBRATION_STATUS
Name Null? Type
—————————————– ——– —————————-
STATUS VARCHAR2(13)
CALIBRATION_TIME TIMESTAMP(3)

SQL> desc gv$io_calibration_status
Name Null? Type
—————————————– ——– —————————-
INST_ID NUMBER
STATUS VARCHAR2(13)
CALIBRATION_TIME TIMESTAMP(3)

Column explanation:
——————-
STATUS:
IN PROGRESS : Calibration in Progress (Results from previous calibration
run displayed, if available)
READY : Results ready and available from earlier run
NOT AVAILABLE : Calibration results not available.

CALIBRATION_TIME: End time of the last calibration run
DBA table that stores I/O Calibration results

SQL> desc DBA_RSRC_IO_CALIBRATE
Name Null? Type
—————————————– ——– —————————-
START_TIME TIMESTAMP(6)
END_TIME TIMESTAMP(6)
MAX_IOPS NUMBER
MAX_MBPS NUMBER
MAX_PMBPS NUMBER
LATENCY NUMBER
NUM_PHYSICAL_DISKS NUMBER

comment on table DBA_RSRC_IO_CALIBRATE is
‘Results of the most recent I/O calibration’

comment on column DBA_RSRC_IO_CALIBRATE.START_TIME is
‘start time of the most recent I/O calibration’

comment on column DBA_RSRC_IO_CALIBRATE.END_TIME is
‘end time of the most recent I/O calibration’

comment on column DBA_RSRC_IO_CALIBRATE.MAX_IOPS is
‘maximum number of data-block read requests that can be sustained per second’

comment on column DBA_RSRC_IO_CALIBRATE.MAX_MBPS is
‘maximum megabytes per second of maximum-sized read requests that can be
sustained’

comment on column DBA_RSRC_IO_CALIBRATE.MAX_PMBPS is
‘maximum megabytes per second of large I/O requests that
can be sustained by a single process’

comment on column DBA_RSRC_IO_CALIBRATE.LATENCY is
‘latency for data-block read requests’

comment on column DBA_RSRC_IO_CALIBRATE.NUM_PHYSICAL_DISKS is
‘number of physical disks in the storage subsystem (as specified by user)’