Enable Oracle Databases Asynchronous I/O

最新推荐文章于 2021-04-04 06:30:19 发布

转载最新推荐文章于 2021-04-04 06:30:19 发布 · 170 阅读

文章标签：

#数据库

通过分析AWR报告，发现控制文件并行写事件占用了大量时间，导致系统I/O瓶颈。文章提供了减少控制文件副本数量、使用异步I/O和调整控制文件位置的方法来解决此问题。

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

712.jpg