11g_Calibrating_IO

By | 09月24日
Advertisement

11g_Calibrating_IO
11g中有个存储过程可以用来评估存储的IO性能,个人认为这是一个很好的新功能。

==前提
1. timed_statistics=TRUE
2. Datafile的asynch_io为ON,可以用下面的SQL确定
SQL> col name format a50
SQL> SELECT name, asynch_io FROM v$datafile f,v$iostat_file i WHERE f.file# = i.file_no AND filetype_name = 'Data File';
NAME ASYNCH_IO
-------------------------------------------------- ---------
D:APPHZ00540ORADATAORCLSYSTEM01.DBF ASYNC_ON
D:APPHZ00540ORADATAORCLSYSAUX01.DBF ASYNC_ON
D:APPHZ00540ORADATAORCLUNDOTBS01.DBF ASYNC_ON
D:APPHZ00540ORADATAORCLUSERS01.DBF ASYNC_ON

==执行评估
SQL> set serveroutput on
SQL>
SQL> declare
2 max_iops number;
3 max_mbps number;
4 actual_latency number;
5 begin
6 dbms_resource_manager.calibrate_io( 1, 10, max_iops, max_mbps, actual_latency);
7 dbms_output.put_line('max_iops => '||to_char(max_iops));
8 dbms_output.put_line('max_mbps => '||to_char(max_mbps));
9 dbms_output.put_line('actual_latency => '||to_char(actual_latency));
10 end;
11 /

max_iops => 87
max_mbps => 25
actual_latency => 10

PL/SQL procedure successfully completed

SQL> select max_iops, max_mbps, max_pmbps, latency from dba_rsrc_io_calibrate;

MAX_IOPS MAX_MBPS MAX_PMBPS LATENCY
---------- ---------- ---------- ----------
87 25 25 10

==一些说明==
1. 存储过程dbms_resource_manager.calibrate_io的5各参数
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

[@more@]

Similar Posts:

Tags: