Oracle 11g wait event: 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

Sometimes there is a background wait event: db file async I/O submit

This wait is related to oracle's asynchronous io.

There are two parameters related to oracle asynchronous io:

SQL> show parameter filesystem

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

NAME TYPE VALUE
--------------------------------------------------------------------------------------------------
Disk_asynch_io boolean TRUE

The default value of disk_asynch_io is true, but the default value of filesystemio_options is none. In this case, the db file async I/O submit wait event occurs.

If you do not want this wait event to appear, you can use either of the following methods:

1: alter system set disk_asynch_io = false scope = spfile; Disable asynchronous io

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

If you want to enable oracle's asynchronous io, in addition to setting the above two parameters to true and asynch, the operating system must support asynchronous io.

Check whether asynchronous io is allowed in linux:

The other is trace-p <DBWR pid>.

Perform dbwr process tracking. If the operation is pwrite, It is synchronous io. If it is io_getevents, It is asynchronous io.

The following describes the parameters:

FILESYSTEMIO_OPTIONS can be set as follows:

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.

You can see the following table at a Glance:

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

In addition, if disk_asynch_io = false, you can set the dbwr_io_slaves parameter to a value greater than 0 to simulate asynchronous io.

View the asynchronous io of files in the database:

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.

Recommended reading:

How to install Oracle 11g on Linux

Detailed description of the installation process of Oracle 11g Database in Linux

How to install Oracle 11g R2 single-instance database on CentOS 5.6

To install Oracle Clusterware on an Oracle vmvm

Install Oracle 11 GB single-instance database on Linux under vmvm