After reading how inadequate Doug was feeling over his IO subsystem, I decided to see how quick mine was...not that we're getting into a "mine is better than yours" game, but rather to see how mine stacks up against Doug's, bearing in mind his is a 5 disk stripe natively attached to his machine (I'm assuming) and mine is a logical disk attached to a VMWare machine...although admittedly, the PC underneath this logical disk is running, motherboard based, RAID striping, over two physical SATA disks...I just figured it would be interesting to compare.

Obviously, any experiment that goes flawlessly according to a preconceived plan is:

1. Boring
2. Less educational
3. Not normally one I've done - mine always have problems it seems!

I ran the calibration on my VMWare based OpenSuse 10 linux with Oracle 11g and it immediately came up with a problem:

SQL> @io
SQL> SET SERVEROUTPUT ON
SQL> DECLARE
2    lat  INTEGER;
3    iops INTEGER;
4    mbps INTEGER;
5  BEGIN
6  -- DBMS_RESOURCE_MANAGER.CALIBRATE_IO (, , iops, mbps, lat);
7     DBMS_RESOURCE_MANAGER.CALIBRATE_IO (2, 10, iops, mbps, lat);
8
9     DBMS_OUTPUT.PUT_LINE ('max_iops = ' || iops);
10     DBMS_OUTPUT.PUT_LINE ('latency  = ' || lat);
11     dbms_output.put_line('max_mbps = ' || mbps);
12  end;
13  /
DECLARE
*
ERROR at line 1:
ORA-56708: Could not find any datafiles with asynchronous i/o capability
ORA-06512: at "SYS.DBMS_RMIN", line 453
ORA-06512: at "SYS.DBMS_RESOURCE_MANAGER", line 1153
ORA-06512: at line 7

Of course, consulting the manual led me to run this query:

SELECT name, asynch_io
FROM v$datafile f,v$iostat_file i
WHERE f.file#        = i.file_no
AND   filetype_name  = 'Data File'
/

which gave:

SQL> /

NAME                                               ASYNCH_IO
-------------------------------------------------- ---------
/home/oracle/oradata/su11/system01.dbf             ASYNC_OFF
/home/oracle/oradata/su11/sysaux01.dbf             ASYNC_OFF
/home/oracle/oradata/su11/undotbs01.dbf            ASYNC_OFF
/home/oracle/oradata/su11/users01.dbf              ASYNC_OFF
/home/oracle/oradata/su11/example01.dbf            ASYNC_OFF

...or in other words no asynchronous IO available - as the error message had said.

After altering the filesystemio_options parameter to "set_all" and bouncing the instance, a second run of the calibration process seemed to work fine...

SQL> @io
SQL> SET ECHO ON
SQL> SET SERVEROUTPUT ON
SQL> DECLARE
2    lat  INTEGER;
3    iops INTEGER;
4    mbps INTEGER;
5  BEGIN
6  -- DBMS_RESOURCE_MANAGER.CALIBRATE_IO (, , iops, mbps, lat);
7     DBMS_RESOURCE_MANAGER.CALIBRATE_IO (2, 10, iops, mbps, lat);
8
9     DBMS_OUTPUT.PUT_LINE ('max_iops = ' || iops);
10     DBMS_OUTPUT.PUT_LINE ('latency  = ' || lat);
11     dbms_output.put_line('max_mbps = ' || mbps);
12  end;
13  /
max_iops = 72
latency  = 13
max_mbps = 26

PL/SQL procedure successfully completed.

So,my figures are considerably lower than those Doug achieved:

max_iops = 112
latency  = 8
max_mbps = 32

...but not too bad I guess considering the fact that mine is a VM and the hardware I'm running is more humble...no seriously, size does not matter!

Labels: dba, errors, tuning