在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 <DBWR pid>
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'.
|