Chinaunix首页 | 论坛 | 博客
  • 博客访问: 2896031
  • 博文数量: 599
  • 博客积分: 16398
  • 博客等级: 上将
  • 技术积分: 6875
  • 用 户 组: 普通用户
  • 注册时间: 2009-11-30 12:04
个人简介

WINDOWS下的程序员出身,偶尔也写一些linux平台下小程序, 后转行数据库行业,专注于ORACLE和DB2的运维和优化。 同时也是ios移动开发者。欢迎志同道合的朋友一起研究技术。 数据库技术交流群:58308065,23618606

文章分类

全部博文(599)

文章存档

2014年(12)

2013年(56)

2012年(199)

2011年(105)

2010年(128)

2009年(99)

分类: Oracle

2011-10-24 18:59:35

在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'.
 
 
阅读(4192) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~