CALIBRATE_IO测试

最新推荐文章于 2021-10-10 15:45:31 发布

bowshy

最新推荐文章于 2021-10-10 15:45:31 发布

阅读量2.2k

点赞数

分类专栏：技术

本文链接：https://blog.csdn.net/lubaoxian/article/details/18330527

版权

技术专栏收录该内容

13 篇文章 0 订阅

订阅专栏

在oracle 11g及更高版本中,参数 parallel_io_cap 被设置成true,oracle将限制并行度为IO子系统能支持的大小,使用前需先使用过程 dbms_resource_manager.calibrate_io计算出IO子系统的限制.

以下为计算IO子系统限制过程:

1. 先检查 FILESYSTEMIO_OPTIONS 参数,是否为ASYNCH 或 SETALL,若不, 则修改为其中一个

Oracle can take advantage of direct I/O and asynchronous I/O on supported platforms using the FILESYSTEMIO_OPTIONS parameter, whose possible values are listed below

ASYNCH - Enabled asynchronous I/O where possible.
DIRECTIO- Enabled direct I/O where possible.
SETALL- Enabled both direct I/O and asynchronous I/O where possible.
NONE - Disabled both direct I/O and asynchronous I/O.

修改 FILESYSTEMIO_OPTIONS=SETALL
SQL> show parameter FILESYS

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
filesystemio_options string none
SQL> alter system set filesystemio_options=setall scope=spfile;

System altered.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
SQL> show parameter filesystemio_options;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
filesystemio_options string SETALL

SQL> col name format a50
SELECT name, asynch_io FROM v$datafile f,v$iostat_file i
2 WHERE f.file# = i.file_no
3 AND filetype_name = 'Data File'
4 /

NAME ASYNCH_IO
-------------------------------------------------- ---------
/u01/app/oracle/oradata/orcl/system01.dbf ASYNC_ON --filesystemio_options为NONE时,所有值都为ASYNC_OFF
/u01/app/oracle/oradata/orcl/sysaux01.dbf ASYNC_ON
/u01/app/oracle/oradata/orcl/undotbs01.dbf ASYNC_ON
/u01/app/oracle/oradata/orcl/users01.dbf ASYNC_ON
/oradata/tbs_tpss_data.01 ASYNC_ON
/oradata/tbs_catalog.dbf ASYNC_ON
/oradata/tbs_tpss_data.02 ASYNC_ON
/oradata/tbs1.dbf ASYNC_ON
/oradata/tbs2.dbf ASYNC_ON
/oradata/tbs3.dbf ASYNC_ON

/oradata/tbs_tpss_data.03 ASYNC_ON

2. 检查 timed_statistics 设置为 TRUE

SQL> show parameter timed_statistics;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
timed_statistics boolean TRUE

3. 执行存储过程 DBMS_RESOURCE_MANAGER.CALIBRATE_IO

set serveroutput on;
declare
lat integer;
iops integer;
mbps integer;
begin
dbms_resource_manager.calibrate_io(2,10,iops,mbps,lat);
dbms_output.put_line('max_iops='||iops);
dbms_output.put_line('latency='||lat);
dbms_output.put_line('max_mbps='||mbps);
end;
/

max_iops=83
latency=11
max_mbps=31

PL/SQL procedure successfully completed.

同样可在以下视图查看结果:
SQL> select * from V$IO_CALIBRATION_STATUS;

STATUS CALIBRATION_TIME
------------- ---------------------------------------------------------------------------
READY 28-DEC-13 01.37.25.103 PM

SQL> select * from DBA_RSRC_IO_CALIBRATE;

START_TIME END_TIME MAX_IOPS MAX_MBPS MAX_PMBPS LATENCY NUM_PHYSICAL_DISKS
-------------------- -------------------- ---------- ---------- ---------- ---------- ------------------
28-DEC-13 01.32.23.9 28-DEC-13 01.37.25.1 83 31 33 11 2
93269 PM 02664 PM