CNDBA社区
之前的博客，我们看了几种通过工具来测试DB性能的，其实在Oracle 数据库中可也以使用dbms_resource_manager.calibrate_io 包来测试IO 性能。
IO压力测试工具 — IOMETER 使用说明
https://www.cndba.cn/dave/article/111
IO压力测试工具 — FIO 使用说明
https://www.cndba.cn/dave/article/104
数据库压力测试工具 — Hammerdb 使用说明
https://www.cndba.cn/dave/article/106
如果使用该包测试，那么在12c中必须在CDB中进行，PDB不支持。DBMS_RESOURCE_MANAGER.CALIBRATE_IO 过程通过读数据文件的方式来计算最大的IOPS和MBPS，所以最接近真实环境。 对于RAC，也是同时在所有实例上运行。
1. 必备条件
（1）必须用SYSDBA 权限执行。
（2）timed_statistics 参数必须为ture。
SQL> show parameter timed_statistics
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
timed_statistics boolean TRUE
（3）使用文件系统时必须启用异步IO
设置FILESYSTEMIO_OPTIONS 初始化参数为SETALL。
SQL> alter system set filesystemio_options=asynch scope=spfile sid='*';
System altered.
SQL> startup force
确认所有的数据文件启用异步IO：
set pages 300 lines 140
col name for a70
/* Formatted on 2014/7/30 10:51:05 (QP5 v5.185.11230.41888) */
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/sddev4/datafile/system.256.717334689 ASYNC_ON
+DATA/sddev4/datafile/system.256.717334689 ASYNC_ON
+DATA/sddev4/datafile/sysaux.257.717334691 ASYNC_ON
+DATA/sddev4/datafile/undotbs1.258.717334693 ASYNC_ON
+DATA/sddev4/datafile/users.259.717334693 ASYNC_ON
+DATA/sddev4/datafile/example.264.717335045 ASYNC_ON
+DATA/sddev4/datafile/undotbs2.265.717335643 ASYNC_ON
+DATA/sddev4/datafile/benchmark01.dbf ASYNC_ON
2. 计算IO
DBMS_RESOURCE_MANAGER.CALIBRATE_IO 过程需要输入2个参数：
（1） number of physical disks (default is 1) ，对于磁盘组，只算DATA 的disk数量，不包含FRA磁盘组。
（2）maximum tolerable latency in milliseconds for database-block-sized I/O request (default is 20).
输出有如下三部分：
1) Maximum number of I/O request per second that can be sustained. The I/O requests are randomly-distributed, database-block-sized reads.
2) Maximum throughput of I/O that can be sustained, expressed in megabytes per second. The I/O request are randomly distributed, 1 megabyte reads.
3) Average latency of database-block-sized I/O requests at max_iops rate, expressed in milliseconds.
Below is an example of calling the procedure to do I/O calibration. My ASM disk group for the data files has three disks, therefore 3 for the 1st input. The intended latency is 10 milliseconds.
/* Formatted on 2014/7/30 11:01:46 (QP5 v5.185.11230.41888) */
SET SERVEROUTPUT ON
DECLARE
lat INTEGER;
iops INTEGER;
mbps INTEGER;
BEGIN
DBMS_RESOURCE_MANAGER.CALIBRATE_IO (NUM_PHYSICAL_DISKS => 1,
MAX_LATENCY => 10,
MAX_IOPS => iops,
MAX_MBPS => mbps,
ACTUAL_LATENCY => lat);
DBMS_OUTPUT.PUT_LINE ('max_iops = ' || iops);
DBMS_OUTPUT.PUT_LINE ('latency = ' || lat);
DBMS_OUTPUT.put_line ('max_mbps = ' || mbps);
END;
/
max_iops = 197678
latency = 0
max_mbps = 4481
这个需要10分钟左右，可以通过如下视图查看状态：
查看状态信息：
SQL> select * from v$io_calibration_status;
STATUS CALIBRATION_TIME
------------- ---------------------------------------------------------------------------
IN PROGRESS 30-JUL-14 10.41.55.357 AM
SQL> set pages 200
SQL> col name for a40
SQL>set lin 130
SELECT name,
small_read_megabytes,
small_read_reqs,
large_read_megabytes,
large_read_reqs,
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 SMALL_READ_MEGABYTES SMALL_READ_REQS LARGE_READ_MEGABYTES LARGE_READ_REQS ASYNCH_IO
---------------------------------------- -------------------- --------------- -------------------- ------------
/oradata/datafile/dave/system01.dbf 398 50547 20 37 ASYNC_ON
/oradata/datafile/dave/system01.dbf 0 0 0 0 ASYNC_ON
/oradata/datafile/dave/sysaux01.dbf 261 32818 0 0 ASYNC_ON
/oradata/datafile/dave/undotbs01.dbf 10951 1401753 0 0 ASYNC_ON
/oradata/datafile/dave/users01.dbf 2 225 0 0 ASYNC_ON
/oradata/datafile/dave/tpcctab01.dbf 14532 1860147 0 0 ASYNC_ON
/oradata/datafile/dave/tpcctab09.dbf 14522 1858850 0 0 ASYNC_ON
/oradata/datafile/dave/tpcctab08.dbf 14527 1859516 0 0 ASYNC_ON
/oradata/datafile/dave/tpcctab07.dbf 14537 1860823 0 0 ASYNC_ON
/oradata/datafile/dave/tpcctab06.dbf 14634 1873228 0 0 ASYNC_ON
/oradata/datafile/dave/tpcctab05.dbf 14651 1875328 0 0 ASYNC_ON
/oradata/datafile/dave/tpcctab04.dbf 14627 1872254 0 0 ASYNC_ON
/oradata/datafile/dave/tpcctab03.dbf 14644 1874407 0 0 ASYNC_ON
/oradata/datafile/dave/tpcctab02.dbf 14647 1874834 0 0 ASYNC_ON
完成之后，可以直接查询dba_rsrc_io_calibrate视图查询之前的结果：
select * from dba_rsrc_io_calibrate;
版权声明：本文为博主原创文章，未经博主允许不得转载。
