Part 2: This is part 2 of series I/O tuning
To test the I/O throughput and latency:
Step 1: Verify if asynchronous I/O is enabled by query
set linesize 130
col name format a70
select
name
, asynch_io
from
v$datafile d
inner join v$iostat_file f on d.file# = f.file_no;
NAME ASYNCH_IO
---------------------------------------------------------------------- ---------
/u02/oradata/TESTDB/system01.dbf ASYNC_OFF
/u02/oradata/TESTDB/system01.dbf ASYNC_OFF
/u02/oradata/TESTDB/sysaux01.dbf ASYNC_OFF
/u02/oradata/TESTDB/undotbs01.dbf ASYNC_OFF
/u02/oradata/TESTDB/users01.dbf ASYNC_OFF
Step 2: set the direct and asynchronous I/O:
sqlplus / as sysdba
SQL>alter system set filesystemio_options=setall scope=spfile;
SQL>shutdown immediate
SQL>startup
Step 3: verify asynch I/O
NAME ASYNCH_IO
---------------------------------------------------------------------- ---------
/u02/oradata/TESTDB/system01.dbf ASYNC_ON
/u02/oradata/TESTDB/system01.dbf ASYNC_ON
/u02/oradata/TESTDB/sysaux01.dbf ASYNC_ON
/u02/oradata/TESTDB/undotbs01.dbf ASYNC_ON
/u02/oradata/TESTDB/users01.dbf ASYNC_ON
Step 4: run the I/O calibration
SET SERVEROUTPUT ON
DECLARE
lat INTEGER;
iops INTEGER;
mbps INTEGER;
BEGIN
DBMS_RESOURCE_MANAGER.CALIBRATE_IO(
1 /* # of disks */
, 10 /* maximum tolerable latency in milliseconds */
, iops /* I/O rate per second */
, mbps /* throughput, MB per second */
, lat /* actual latency in milliseconds */
);
DBMS_OUTPUT.PUT_LINE('max_iops = ' || iops);
DBMS_OUTPUT.PUT_LINE('latency = ' || lat);
DBMS_OUTPUT.PUT_LINE('max_mbps = ' || mbps);
END;
/
max_iops = 161 [I/O rate per secomd]
latency = 11 [ I/O latency in miliseconds]
max_mbps = 91 [ maximum throughput read]
Step 5: check the performed I/O size:
SQL> select
2 d.name
3 , f.file_no
4 , f.small_read_megabytes
5 , f.small_read_reqs
6 , f.large_read_megabytes
7 , f.large_read_reqs
8 from
9 v$iostat_file f
10 inner join v$datafile d on f.file_no = d.file#
11 ;
NAME FILE_NO SMALL_READ_MEGABYTES SMALL_READ_REQS LARGE_READ_MEGABYTES LARGE_READ_REQS
----------------------------------- ---------- -------------------- --------------- -------------------- ---------------
/u02/oradata/TESTDB/system01.dbf 1 277 34308 2866 3033
/u02/oradata/TESTDB/system01.dbf 1 0 17 0 0
/u02/oradata/TESTDB/sysaux01.dbf 2 191 24235 2541 2541
/u02/oradata/TESTDB/undotbs01.dbf 3 269 34381 3893 3893
/u02/oradata/TESTDB/users01.dbf 4 302 34763 579 579
Posts
