Latch, mutex and beyond

July 29, 2010

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

Filed under: 11.2,Uncategorized — andreynikolaev @ 5:05 pm

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.

About these ads

1 Comment »

  1. Thank you for the post. I am also having the same issue , got confused by this event. Oracle should do a better job of documenting their wait events.

    Comment by amalendu chakraborty — February 5, 2013 @ 9:29 pm | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

The Rubric Theme. Blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 65 other followers

%d bloggers like this: