Monthly Archives: September 2007

CALIBRATE_IO Poll

After Doug’s comment on my earlier blog on CALIBRATE_IO, and after seeing Kevin using Mr Poll to do a RAC poll, I’ve now created a poll for the results people are getting from this CALIBRATE_IO routine in 11g.

If you’d like to contribute please visit using the following link:

Oracle 11g CALIBRATE_IO Results

No prizes for the “biggest”…Kevin is bound to have something in his lab that beats us all! ;-)

Thanks

11g IO Calibration tool

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!

11g PX tracefiles now have the tracefile identifier on them

Now that I’ve got 11g up and running on OpenSuse 10.2 on a VMWare 6 VM, I’ve had time to do some playing with the latest and greatest release and the first thing I’ve noticed, when running some of Doug’s PX test scripts, is that the trace files generated for PX slaves now have the Tracefile Identifier appended to their name, making it easier to see which OS Process (PID) was responsible for the creation of the trace file – makes things a little easier and clearer.

In 10gR2 (10.2.0.2.0 specifically) the trace files would come out with names in this format:

_ _.trc

e.g. fred_p001_6789.trc

In 11gR1 (11.1.0.6.0 specifically) the trace files come out with names in this format:

_ __
.trc

e.g. fred_p001_5678_jeff.trc

This assumes you’ve set the tracefile identifier in the first place, otherwise that bit won’t be present. Use the following to set it, choosing whatever identifier you require of course:

alter session set tracefile_identifier='jeff';

It was interesting that the location of such files has also changed due to the implementation of Automatic Diagnostic Repository (ADR). More information on that here.