Strange “db file async I/O submit” wait event

This post will not be directly related to the blog theme. I would like to discuss “db file async I/O submit” wait event. This new event was introduced in Oracle 11.2. So far it have not been described in Oracle documentation and Metalink.

At the beginning of this story, this event became the topmost background wait for one production instance under HP-UX:

                                                             Avg
                                        %Time Total Wait    wait    Waits   % bg
Event                             Waits -outs   Time (s)    (ms)     /txn   time
-------------------------- ------------ ----- ---------- ------- -------- ------
db file async I/O submit        151,159     0     35,625     236      0.7   96.3
log file parallel write         427,728     0        308       1      2.0     .8
...

This looks mystique. HP-UX not supports AIO for filesystem at all!

This wait appeared in the “Background Wait Events” AWR report section. According to v$session_event each of 10 DBWRs waited for it almost all the time.

Further investigations revealed the problem with extremely slow disk writes. It occurs that Oracle Database Administrator’s Reference 11g Release 2 (11.2) for Linux and UNIX-Based Operating Systems explicitly states that for HP-UX the “…DISK_ASYNCH_IO parameter must be set to FALSE when the database files reside on file system. This parameter must be set to TRUE only when the database files reside on raw partitions.”

After restarting instance with DISK_ASYNCH_IO=FALSE, the topmost background wait become “db file parallel write” as expected. Episodes of abnormal disk response times are still under investigation.

But the question remains – what mean the “db file async I/O submit” without AIO?

Let us experiment with Oracle 11.2.0.1.1 on Linux X86. Solaris shows the same behaviour.

I set various values for DISK_ASYNCH_IO and FILESYSTEMIO_OPTIONS, and traced system calls of DBWR with strace. According to Metalink Note 237299.1 this allows to check whether AIO is really used. At the same time, I monitored the event that DBWR waited for using query:

SELECT * FROM v$session_event
   WHERE sid IN (SELECT sid FROM v$session WHERE program LIKE '%DBW%');

The results were intriguing:

disk_asynch_io	filesystemio_options	strace –p <DBWR pid>	AIO used	DBRW waits on:
FALSE	NONE	pwrite64(22,	NO	db file parallel write
FALSE	ASYNCH	pwrite64(22, …	NO	db file parallel write
TRUE	ASYNCH	io_submit(7557120,… io_getevents(7557120,…	YES	db file parallel write
TRUE	NONE	pwrite64(20, …	NO	db file async I/O submit

This conforms to the conclusion of Metalink Note 432854.1:

“… disk_asynch_io is a kind of master switch, which turns on or off Async I/O to database files on any type of storage, whether it’s raw device or filesystem. The filesystemio_options parameter gives finer control over I/O to database files on filesystems. …”

The experiment demonstrated that Oracle 11.2 DBWR behave differently from previous releases. If  AIO is enabled by disk_asynch_io but can not be used  due to filesystemio_options or platform limitations, the DBWR will wait on “db file async I/O submit” event until IO completion.

This may be a bug, or intended change. In any case synchronous “db file async I/O submit” looks strange for me.

Probably “db file parallel read” may also show the same behaviour.

In summary, for tuning purpose, the “db file async I/O submit” should be treated as   “db file parallel write” in previous releases.