Understanding Oracle Write Complete Waits

The write complete waits event has two parameters: file# and block#. In Oracle Database 10g, this wait event falls under the Configuration wait class. Keep the following key thought in mind when dealing with the write complete waits event.

• The Oracle session is prevented from modifying a block that is being written by the DBWR process.

 Common Causes, Diagnosis, and Actions

The write complete waits latency is symptomatic of foreground processes needing to modify blocks that are marked by the DBWR process as “being written.” Blocks that are marked “being written” cannot be modified until they are written to disk and the flags are cleared. If a session spends a lot of time waiting on the write complete waits event, it is usually due to one or a combination of the following reasons:

•  Slow I/O subsystem causing high DBWR write time
•  MTTR is too short
•  DBWR write batch size is too large

When the effectiveness of the DBWR process is impacted by the I/O subsystem, it can have a domino effect on foreground processes in terms of write complete waits and free buffer waits latencies. The write complete waits latency is usually a secondary problem. You should focus on the average wait time of the db file parallel write event. Any improvement to the DBWR average write time should also minimize the write complete waits latency.

A short MTTR can produce a hyperactive DBWR process. While an active DBWR process is beneficial for supplying clean buffers, it tends to write hot buffers out repeatedly. Sessions that actively perform DML operations can find themselves waiting on the write complete waits event. Therefore, make sure the FAST_START_ MTTR_TARGET parameter is not set too low. One way to find out is to trace the DBWR process with the event 10046 at level 8 and observe the write calls (db file parallel write) over a period of time. If you continually see DBWR writing out before its 3-second timeout, chances are it is overly active.

Beginning in Oracle8i Database, the default DBWR write batch size is 204 blocks. This is changed from 4096 blocks in prior versions. This means starting in Oracle8i Database, the DBWR write batch size should no longer be a factor that contributes to the write complete waits contention.