- 调用该存储过程需要用到SYSDBA权限
- TIME_STATISTICS 参数为true:
SQL> show parameter timed_statistics;
NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ timed_statistics boolean TRUE
- 必须打开异步IO;注意在使用文件系统时,FILESYSTEMIO_OPTIONS需设为SETALL或ASYNC,否则Oracle不会启用异步IO。
- 可以通过以下查询检验是否启用了异步IO:
SQL> SELECT NAME, ASYNCH_IO
2 FROM V$DATAFILE F, V$IOSTAT_FILE I
3 WHERE F.FILE# = I.FILE_NO
4 AND FILETYPE_NAME = 'Data File';
NAME ASYNCH_IO
-------------------------------------------------------------------------------- ---------
D:\TOOLS\ADMINSTRATOR\11G\ORADATA\PROD\DATAFILE\O1_MF_SYSTEM_65DN8HXT_.DBF ASYNC_ON
D:\TOOLS\ADMINSTRATOR\11G\ORADATA\PROD\DATAFILE\O1_MF_SYSAUX_65DN8J18_.DBF ASYNC_ON
D:\TOOLS\ADMINSTRATOR\11G\ORADATA\PROD\DATAFILE\O1_MF_UNDOTBS1_65DN8J1S_.DBF ASYNC_ON
D:\TOOLS\ADMINSTRATOR\11G\ORADATA\PROD\DATAFILE\O1_MF_USERS_65DN8J2X_.DBF ASYNC_ON
IO Calibration特性可以通过DBMS_RESOURCE_MANAGER.CALIBRATE_IO存储过程调用;该过程会对Oracle数据文件引发一系列IO敏感的只读工作负载(由1MB大小的随机IO组成),从而判断存储系统所能持续的最大IOPS(每秒最大IO请求数)和MBPS(每秒IO传输速率)。为了使结果更具代表性,应当保持IO Calibration测试过程中整个数据库是空闲的,没有其他IO负载损耗。 让我们具体来看一下CALIBRATE_IO存储过程的调用方法:
-- Initiate an I/O calibration
--
-- Input arguments:
-- 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
--
-- Output arguments:
-- 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.
--
PROCEDURE 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用以指定数据库存储所用物理磁盘的近似数;max_latency指定了客户所能容忍的最大IO 延迟时间,单位为ms;一般我们认为IO平均等待在10ms左右是一种较好的表现,远大于这个数字往往说明IO负载过高了,这里我们假定15ms是我们的deadline。
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, 15, 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 = 127
latency = 15
max_mbps = 38
PL/SQL procedure successfully completed
/*平均延迟为15ms时,最大iops为127,mbps为38*/
/*执行过程中也可以通过V$IO_CALIBRATION_STATUS视图了解进度*/
SQL> select * from V$IO_CALIBRATION_STATUS;
STATUS CALIBRATION_TIME
------------- --------------------------------------------------------------------------------
IN PROGRESS 11-8月 -10 12.02.35.121 下午
SQL> alter session set nls_timestamp_format='HH24:MI';
Session altered
SQL> col start_time for a10;
SQL> col end_time for a10;
SQL> select * from DBA_RSRC_IO_CALIBRATE;
START_TIME END_TIME MAX_IOPS MAX_MBPS MAX_PMBPS LATENCY NUM_PHYSICAL_DISKS
---------- ---------- ---------- ---------- ---------- ---------- ------------------
12:55 12:59 127 38 14 15 2
/*DBA_RSRC_IO_CALIBRATE视图记录了IO CALIBRATION的历史信息*/
[gview file="http://youyus.com/wp-content/uploads/resource/calibrate_io.pdf"]