Translate

Search This Blog

I/O tuning in Oracle : test the I/O throughput and latency:

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