oracle 异步io与oracle 11g等待事件：db file async I/O submit

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

 有时候出现后台等待事件：db file async I/O submit

该等待跟oracle的异步io有关。

与oracle异步io相关的参数有2个：

SQL> show parameter filesystem

NAME                                 TYPE                             VALUE
------------------------------------ -------------------------------- ------------------------------
filesystemio_options                 string                           none
SQL> show parameter disk_asynch

NAME                                 TYPE                             VALUE
------------------------------------ -------------------------------- ------------------------------
disk_asynch_io                       boolean                          TRUE

disk_asynch_io 这个参数默认是true，但是filesystemio_options默认是none。这种情况会出现db file async I/O submit等待事件。

如果不想这个等待事件出现，可以采取两种方法：

1：alter system set disk_asynch_io=false scope=spfile;  关闭异步io

2：alter system set filesystemio_options=asynch scope=spfile;

如果想开启oracle的异步io，除了设置上面的两个参数为true和asynch以外，还得操作系统支持异步io才行。

检查linux是否允许异步io：

另一个是：trace –p <DBWR pid>

做dbwr进程的跟踪，如果操作是pwrite，那么是同步io，如果是io_getevents，那么是异步io。

下面是参数的解释：

FILESYSTEMIO_OPTIONS可以设置的值如下：

ASYNCH: enable asynchronous I/O on file system files, which has no timing requirement for transmission.

DIRECTIO: enable direct I/O on file system files, which bypasses the buffer cache.

SETALL: enable both asynchronous and direct I/O on file system files.

NONE: disable both asynchronous and direct I/O on file system files.

看下面的表格就一目了然了：

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/io_getevents	YES	db file parallel write
TRUE	NONE	pwrite64	NO	db file async I/O submit

另外，如果disk_asynch_io=false 的话，可以设置参数dbwr_io_slaves为一个大于0的值来模仿异步io

查看数据库中文件的异步io情况：

SQL> select file_no,filetype_name,asynch_io from v$iostat_file;

   FILE_NO FILETYPE_NAME                ASYNCH_IO
---------- ---------------------------- ---------
         0 Other                        ASYNC_OFF
         0 Control File                 ASYNC_OFF
         0 Log File                     ASYNC_OFF
         0 Archive Log                  ASYNC_OFF
         0 Data File Backup             ASYNC_OFF
         0 Data File Incremental Backup ASYNC_OFF
         0 Archive Log Backup           ASYNC_OFF
         0 Data File Copy               ASYNC_OFF
         0 Flashback Log                ASYNC_OFF
         0 Data Pump Dump File          ASYNC_OFF
         1 Data File                    ASYNC_OFF
         2 Data File                    ASYNC_OFF
         3 Data File                    ASYNC_OFF
         4 Data File                    ASYNC_OFF
         5 Data File                    ASYNC_OFF
         6 Data File                    ASYNC_OFF

16 rows selected.

 如需转载，请注明出处：http://blog.csdn.net/nanaranran/article/details/17370935