11G db file async I/O submit等待事件
<DIV>在11G的环境中，可能会遇到<FONT color=#f00000>db file async I/O submit等待事件</FONT></DIV><DIV> </DIV>
<DIV>SQL> select event from v$session_wait where wait_class<>'Idle';</DIV>
<DIV>EVENT<BR>----------------------------------------------------------------<BR>SQL*Net message to client<BR>Data file init write<BR><FONT color=#f00000>db file async I/O submit<BR>db file async I/O submit<BR></FONT>log file parallel write<BR>buffer busy waits<BR>db file sequential read<BR>db file sequential read</DIV>
<DIV> </DIV>
<DIV>在11G中，默认异步IO是打开的：<BR>SQL> show parameter disk </DIV>
<DIV>NAME TYPE VALUE<BR>------------------------------------ ----------- ------------------------------<BR>asm_diskgroups string<BR>asm_diskstring string<BR><FONT color=#f00000>disk_asynch_io boolean TRUE</FONT></DIV>
<DIV> </DIV>
<DIV>但是实际filesystemio_options并没有设置<BR>SQL> show parameter filesystem</DIV>
<DIV>NAME TYPE VALUE<BR>------------------------------------ ----------- ------------------------------<BR><FONT color=#f00000>filesystemio_options string none</FONT></DIV>
<DIV><FONT color=#f00000><BR></FONT>SQL> select * from v$version;</DIV>
<DIV>BANNER<BR>--------------------------------------------------------------------------------<BR>Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production<BR>PL/SQL Release 11.2.0.1.0 - Production<BR>CORE 11.2.0.1.0 Production<BR>TNS for Linux: Version 11.2.0.1.0 - Production<BR>NLSRTL Version 11.2.0.1.0 - Production</DIV>
<DIV> </DIV>
<DIV>metalink上说：</DIV>
<DIV><STRONG>'db file async I/O submit' should be treated as 'db file parallel write' in previous releases</STRONG>.</DIV>
<DIV> </DIV>
<DIV>通过修改参数<FONT color=#f00000>disk_asynch_io </FONT><FONT color=#000000>为FALSE,关闭异步IO</FONT></DIV>
<DIV><FONT color=#f00000></FONT> </DIV>
<DIV>SQL> alter system set disk_asynch_io =false scope=spfile;</DIV>
<DIV> </DIV>
<DIV>System altered.</DIV>
<DIV> </DIV>
<DIV>重启数据库，至此此等待事件不在出现。</DIV>
<DIV>当然还可以通过修改参数filesystemio_options</DIV>
<DIV> </DIV>
<DIV>alter system set filesystemio_options =asynch scope=spfile。</DIV>
<DIV> </DIV>
<DIV>以下是metalink 的资料：</DIV>
<DIV> </DIV>
<DIV><STRONG>'db file async I/O submit' when FILESYSTEMIO_OPTIONS=NONE </STRONG></DIV>
<DIV>--------------------------------------------------------------------------------<BR> <BR> 修改时间 17-DEC-2010 类型 PROBLEM 状态 MODERATED </DIV>
<DIV><STRONG>In this Document</STRONG></DIV>
<DIV><BR> Symptoms<BR> Cause<BR> Solution<BR> References</DIV>
<DIV> </DIV>
<DIV>--------------------------------------------------------------------------------</DIV>
<DIV><BR>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. </DIV>
<DIV> </DIV>
<DIV><STRONG>Applies to: </STRONG></DIV>
<DIV><BR>Oracle Server - Enterprise Edition - Version: 11.2.0.2 and later <BR>Information in this document applies to any platform.</DIV>
<DIV> </DIV>
<DIV><STRONG>Symptoms</STRONG></DIV>
<DIV><BR>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'.</DIV>
<DIV>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:</DIV>
<DIV> </DIV>
<DIV><FONT color=#f00000>cat /proc/slabinfo |grep kio<BR>kioctx 50 84 320 12 1 : tunables 54 27 8 : slabdata 7 7 0<BR>kiocb 0 0 256 15 1 : tunables 120 60 8 : slabdata 0 0 0</FONT></DIV>
<DIV><FONT color=#f00000><BR></FONT>A second check is to collect the OS debugger trace on a database writer process:</DIV>
<DIV> </DIV>
<DIV>E.g.</DIV>
<DIV>trace –p <DBWR pid></DIV>
<DIV><BR>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.</DIV>
<DIV> </DIV>
<DIV><STRONG>Cause</STRONG></DIV>
<DIV><STRONG><BR></STRONG>This is the expected behavior.</DIV>
<DIV>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.</DIV>
<DIV> </DIV>
<DIV>The tests show the following behavior:</DIV>
<DIV>disk_asynch_io filesystemio_options strace DBWR AIO DBWR waits<BR>FALSE NONE pwrite64 NO db file parallel write<BR>FALSE ASYNCH pwrite64 NO db file parallel write<BR>TRUE ASYNCH io_submit/ YES db file parallel write<BR> io_getevents<BR>TRUE NONE pwrite64 NO db file async I/O submit</DIV>
<DIV> </DIV>
<DIV><STRONG>Solution</STRONG></DIV>
<DIV><BR>'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'.<BR></DIV>
<DIV> </DIV>
<DIV> </DIV>
页:
[1]