暂无图片
暂无图片
1
暂无图片
暂无图片
暂无图片

oracle测试IO

原创 大柏树 2022-04-24
1927

一.背景

oracle官方提供了DBMS_RESOURCE_MANAGER.CALIBRATE_IO存储过程,用于测试当前数据库实例的I/O性能。

二.介绍

2.1.DBMS_RESOURCE_MANAGER.CALIBRATE_IO参数如下:

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

2.2.常用的输入参数具体如下

  • num_physical_disks:当前系统中存储盘的个数,根据实际情况填写即可。
  • max_latency:所能容忍的最大延迟(单位为ms),对于OLTP系统,建议将该值设置在20以内。

2.3.测试之前需要确认当前环境是否开启了异步I/O,命令如下:

SQL>SELECT d. name,i.asynch_io FROM v$datafile d,v$iostat_file i WHERE d. file#=i. file_no AND i.filetype_name='Data File'; NAME ASYNCH_IO +DATA/jason/datafile/system01. dbf ASYNCOFF +DATA/jason/datafile/sysaux01. dbf ASYNCOFF +DATA/jason/datafile/undotbs01. dbf ASYNCOFF +DATA/jason/datafile/users01. dbf ASYNCOFF --AYNC_OFF表示未开启,开启异步I/O(需要重启数据库才能生效) SQL> ALTER SYSTEM SET   filesystemio_options=setall SCOPE=SPFILE; SQL>set  serveroutput  on; SQL>DECLARE lat INTEGER; iops INTEGER; mbps INTEGER; BEGIN DBMS_RESOURCE_MANAGER.CALIBRATE_IO(1, 20, iops, mbps, lat); DBMS_OUTPUT.PUT_LINE('max _ iops=' || /iops); DBMS_OUTPUT.PUT_LINE('latency=' || 1at); dbms_output.put line('max _ mbps=' || mbps); end;

2.4.

输出结果具体如下:

  • Max IOPS=51800 --表示每秒可以维持的最大I/O请求数。
  • Max MBPS = 654 --表示可以维持的最大I/O吞吐量
  • Latency = 8 --actual_latency:以max_iops表示当前I/O请求的平均延迟,单位为ms。
    同样,我么也可以通过视图v$io_calibration_status查看测试结果。

三.以下是我在墨天轮21c实训环境测试的过程:

SQL> set line 200 SQL> col name for a120 SQL> SELECT d. name,i.asynch_io FROM v$datafile d,v$iostat_file i WHERE d. file#=i. file_no AND i.filetype_name='Data File'; 2 3 4 NAME ASYNCH_IO ------------------------------------------------------------------------------------------------------------------------ --------- /data/app/oracle/oradata/orcl/system01.dbf ASYNC_OFF /data/app/oracle/oradata/orcl/sysaux01.dbf ASYNC_OFF /data/app/oracle/oradata/orcl/undotbs01.dbf ASYNC_OFF /data/app/oracle/oradata/orcl/users01.dbf ASYNC_OFF /data/app/oracle/oradata/orcl/example01.dbf ASYNC_OFF SQL> ALTER SYSTEM SET filesystemio_options=setall SCOPE=SPFILE; SQL> ALTER SYSTEM SET filesystemio_options=setall SCOPE=SPFILE; System altered. SQL> shutdown abort; ORACLE instance shut down. SQL> startup; ORACLE instance started. Total System Global Area 1586708480 bytes Fixed Size 2253624 bytes Variable Size 989859016 bytes Database Buffers 587202560 bytes Redo Buffers 7393280 bytes Database mounted. Database opened. SQL> SQL> SQL> set line 200 SQL> col name for a120 SQL> SELECT d. name,i.asynch_io FROM v$datafile d,v$iostat_file i WHERE d. file#=i. file_no AND i.filetype_name='Data File'; 2 3 4 NAME ASYNCH_IO ------------------------------------------------------------------------------------------------------------------------ --------- /data/app/oracle/oradata/orcl/system01.dbf ASYNC_ON /data/app/oracle/oradata/orcl/sysaux01.dbf ASYNC_ON /data/app/oracle/oradata/orcl/undotbs01.dbf ASYNC_ON /data/app/oracle/oradata/orcl/users01.dbf ASYNC_ON /data/app/oracle/oradata/orcl/example01.dbf ASYNC_ON SQL> set serveroutput on; DECLARE lat INTEGER; iops INTEGER; mbps INTEGER; BEGIN DBMS_RESOURCE_MANAGER.CALIBRATE_IO(1, 20, iops, mbps, lat); DBMS_OUTPUT.PUT_LINE('max_iops = ' || iops); DBMS_OUTPUT.PUT_LINE('latency= ' || lat); dbms_output.put_line('max_mbps = ' || mbps); end; / SQL> set serveroutput on; DECLARE lat INTEGER; iops INTEGER; mbps INTEGER; BEGIN DBMS_RESOURCE_MANAGER.CALIBRATE_IO(1, 20, iops, mbps, lat); DBMS_OUTPUT.PUT_LINE('max_iops = ' || iops); DBMS_OUTPUT.PUT_LINE('latency= ' || lat); dbms_output.put_line('max_mbps = ' || mbps); end; /SQL> 2 3 4 5 6 7 8 9 10 11 max_iops = 4993 latency = 18.614 max_mbps = 146 Note: The high I/O latencies from the calibration run indicate that the calibration I/Os are being serviced mostly from disk. If your storage has a cache, you may achieve better results by rerunning. Rerunning may benefit from the storage cache. max_iops = 4993 latency= 19 max_mbps = 146 PL/SQL procedure successfully completed.
最后修改时间:2022-04-24 15:33:30
「喜欢这篇文章,您的关注和赞赏是给作者最好的鼓励」
关注作者
1人已赞赏
【版权声明】本文为墨天轮用户原创内容,转载时必须标注文章的来源(墨天轮),文章链接,文章作者等基本信息,否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容,欢迎发送邮件至:contact@modb.pro进行举报,并提供相关证据,一经查实,墨天轮将立刻删除相关内容。

评论