11G db file async I/O submit等待事件

在11G的环境中，可能会遇到db file async I/O submit等待事件

 

SQL> select event from v$session_wait where wait_class<>'Idle';

EVENT
----------------------------------------------------------------
SQL*Net message to client
Data file init write
db file async I/O submit
db file async I/O submit
log file parallel write
buffer busy waits
db file sequential read
db file sequential read

 

在11G中，默认异步IO是打开的：
SQL> show parameter disk

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
asm_diskgroups                       string
asm_diskstring                       string
disk_asynch_io                       boolean     TRUE

 

但是实际filesystemio_options并没有设置
SQL> show parameter filesystem

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
filesystemio_options                 string      none

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

 

metalink上说：

'db file async I/O submit' should be treated as 'db file parallel write' in previous releases.

 

通过修改参数disk_asynch_io 为FALSE,关闭异步IO

 

SQL> alter system set disk_asynch_io =false   scope=spfile;

 

System altered.

 

重启数据库，至此此等待事件不在出现。

当然还可以通过修改参数filesystemio_options

 

alter system set filesystemio_options =asynch scope=spfile。

 

以下是metalink 的资料：

 

'db file async I/O submit' when FILESYSTEMIO_OPTIONS=NONE [ID 1274737.1]

--------------------------------------------------------------------------------
 
  修改时间 17-DEC-2010     类型 PROBLEM     状态 MODERATED  

In this Document

  Symptoms
  Cause
  Solution
  References

 

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

This document is being delivered to you via Oracle Support's Rapid Visibility (RaV) process and therefore has not been subject to an independent technical review.

 

Applies to:

Oracle Server - Enterprise Edition - Version: 11.2.0.2 and later   [Release: 11.2 and later ]
Information in this document applies to any platform.

 

Symptoms

According to the Oracle documentation, the asynch is disabled for normal file systems if FILESYSTEMIO_OPTIONS=NONE. But, when setting DISK_ASYNCH_IO to TRUE, the wait event list shows 'db file async I/O submit'.

Using for example Note 237299.1 How To Check if Asynchronous I/O is Working On Linux  to check if the asynch is enable shows synchronous IO; the kiocb is 0 running the steps from the note:

 

cat /proc/slabinfo |grep kio
kioctx 50 84 320 12 1 : tunables  54 27 8 : slabdata 7 7 0
kiocb   0  0 256 15 1 : tunables 120 60 8 : slabdata 0 0 0

A second check is to collect the OS debugger trace on a database writer process:

 

E.g.

trace –p

If the IO calls are like pwrite64 then this is synchronous IO and if the IO calls are like io_getevents then this is asynch IO.

 

Cause

This is the expected behavior.

According to unpublished the Bug 9649885 DB FILE ASYNC I/O SUBMIT EVENT NOT TRACKED WHEN DISK_ASYCH_IO = TRUE, when DISK_ASYNCH_IO=TRUE, the wait event 'db file async I/O submit' is posted even if the IO calls cannot be performed asynchronously and this is the current behavior.

 

The tests show the following behavior:

disk_asynch_io filesystemio_options strace DBWR AIO DBWR waits
FALSE          NONE                 pwrite64    NO  db file parallel write
FALSE          ASYNCH               pwrite64    NO  db file parallel write
TRUE           ASYNCH               io_submit/  YES db file parallel write
                                    io_getevents
TRUE           NONE                 pwrite64    NO  db file async I/O submit

 

Solution

'db file async I/O submit' should be treated as 'db file parallel write' in previous releases. Changing the DISK_ASYCH_IO to FALSE removes the wait event 'db file async I/O submit'.