DBA Notes: 2011/10/14

Cheng Li

Enable Oracle Databases Asynchronous I/O

From AWR reports, we found the event “control file parallel write” itself cost 10,480 second, while the system I/O is 10,740 seconds only and the total db time is 16,208 seconds.

Normally there are several possible causes:

-too many log file swaps (can reduce by increasing the log file size)

-the physical disk is very busy (need further study on the mount points)

Following is the existing control files:

/u01/app/oracle/oradata1/CMSPRD/controlfile/o1_mf_53gzjq19_.ctl

/u01/app/oracle/oradata/CMSPRD/controlfile/o1_mf_53gzjq19_.ctl

By investigating the existance of I/O bottlenecks and a feasible way to address the identified 'control file parallel write' wait event.

 -----------------------------------
CONTROLFILE I/O-RELATED WAIT EVENTS
-----------------------------------

These Wait Events occur during I/O to one or all copies of the controlfile.

Frequency of Controlfile access is governed by activities such as Redo Logfile
switching and Checkpointing. Therefore it can only be influenced indirectly
by tuning these activities.

'control file parallel write'
------------------------------------------------------------

This occurs when a server process is updating all copies of the controlfile.
If it is significant, check for bottlenecks on the I/O paths (controllers,
physical disks) of all of the copies of the controlfile.

Possible solutions:

o Reduce the number of controlfile copies to the minimum that ensures
  that not all copies can be lost at the same time.

o Use Asynchronous I/O if available on your platform.

o Move the controlfile copies to less saturated storage locations.


'control file sequential read'
'control file single write'
------------------------------------------------------------

These occur on I/O to a single copy of the controlfile.
If they are significant find out whether the waits are on particular copy
of the controlfile and if so whether its I/O path is saturated.

The following query can be used to find which controlfile is being accessed.
It has to be run when the problem is occuring:

select P1 from V$SESSION_WAIT
where EVENT like 'control file%' and STATUS='WAITING';

Possible solutions:

o Move the problematic controlfile copy to a less saturated storage location.

o Use Asynchronous I/O if available on your platform.

Solution: Use Asynchronous I/O if available on your platform.

We enable the CTMCMSDB Async. I/O, please see the following:

[root@ctmcmsdb ~]# su - oracle
[oracle@ctmcmsdb ~]$ ldd $ORACLE_HOME/bin/oracle|grep libaio
        libaio.so.1 => /usr/lib64/libaio.so.1 (0x00002aaaab92f000)
[oracle@ctmcmsdb ~]$ nm $ORACLE_HOME/bin/oracle |grep io_getevent
                 w io_getevents@@LIBAIO_0.4

To enable async I/O in Oracle, the disk_asynch_io parameter needs to be set to true:

disk_asynch_io=true

Note this parameter is set to true by default in Oracle 9i and 10g:

SQL> show parameter disk_asynch_io;

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

disk_asynch_io                       boolean     TRUE

SQL>

Reference:

http://www.puschitz.com/TuningLinuxForOracle.shtml