db file parallel write

The db file parallel write wait event belongs to the Oracle Database Writer (DBWR) process since it is the only process that writes blocks from the SGA to datafiles. When it is time to write, the DBWR process compiles a set of dirty blocks, hands the batch over to the OS, and waits on the db file parallel write event for the I/O to complete.

Although user sessions never experience the db file parallel write wait event, this doesn't mean that they are never impacted by it. If the write complete waits or free buffer waits event shows up in user sessions, then they may be suffering from the impact of the db file parallel write event.

If a user session needs to modify a block that happens to be in the DBWR write batch, it has to wait on the write complete waits event until that batch of blocks is completely written to disk. If the batch size is large, or the I/O subsystem is slow, the DBWR process will incur additional time waiting for the I/O to complete, and so will the user session that needs the block that is being written.

If user sessions are experiencing the free buffer waits wait event, and the number of waits increases steadily, this means there is a shortage of free blocks in the SGA. This can happen if the Buffer Cache is too small, or DBWR can't keep up with the rate of blocks being dirtied. One of the reasons why the DBWR process can't keep up with dirty blocks is that it spends too much time on the db file parallel write event. Parameters:

• P1=The number of files Oracle is writing to.
• P2=The number of blocks to be written.
• P3=Total number of I/O request same as P2 because multi-block I/O is not used.

Since P1 and P2 report the number of files and blocks instead of the absolute file and block number, a DBA cannot tell which objects are being written. However, the user session that waits on the write complete waits or free buffer waits event does indicate the absolute file and block number in its P1 and P2 values. Common Causes and Actions

The db file parallel write latency is normally a symptom of a slow I/O subsystem or poor I/O configurations. This includes poor layout of database files, bad mount point to I/O controller ratio, wrong stripe size and/or RAID level, and not enough disks (i.e. there are a few high capacity disks versus many lower capacity disks). The DBA needs to look at the average I/O time. A well-laid database and I/O subsystem should provide an average I/O wait that does not exceed 2 centiseconds. If this is an issue, the DBA should review the I/O configuration by mapping out the I/O routes from mount points to controllers, and controllers to physical disk groups, and ensure proper placement of database files. The command for this function is platform specific and unfortunately, often requires administrator privilege. For storage systems configured with the Veritas volume manager on Sun platform, the DBA may be able to use the vxprint –ht command. The DBA should also watch for usage (i.e. I/O throughput and bottlenecks) from the OS level using sar –d, iostat –dxn, or an equivalent tool. If some disks are hit hard for an extended period of time (i.e. almost 100% busy), and the average queue length is greater than 3, then the DBA needs to rearrange some of the database files.

Beyond ensuring the I/O subsystem is properly configured and database files are well placed, the DBA should make non-blocking I/O (DISK_ASYNC_IO = TRUE) available to the DBWR process if the platform supports asynchronous I/O.

A larger write batch increases the DBWR I/O wait time, especially in an environment where datafiles are poorly placed. A sure sign that the write batch is too big is when user sessions start to wait on the write complete waits event. Prior to Oracle 8i, the _DB_BLOCK_WRITE_BATCH parameter determined the DBWR write batch size and the value can be seen in X$KVII. It is listed as DB writer IO clump. In 8i and higher, this parameter was replaced by the _DB_WRITER_CHUNK_WRITES and is listed as DBWR write chunk. A new parameter _DB_WRITER_MAX_WRITES was introduced to limit the number of outstanding DBWR I/Os. The DBA should ensure the batch size is not so large that it causes write complete waits and longer db file parallel write, and also not so small that it causes long dirty queue and free buffer waits. Also, bear in mind the improvements that Oracle made since 8i should put the write batch issue to rest, and DBAs shouldn't have to mess with it. The write complete waits event is prevalent in versions prior to 8i.

Prior to Oracle 8i

select * from x$kvii where kviitag = 'kcbswc';

Oracle 8i and higher

select * from x$kvii where kviitag in ('kcbswc','kcbscw');

When the DB_BLOCK_MAX_DIRTY_TARGET parameter is set too low, it can also cause excessive waits on the db file parallel write and write complete waits events. This parameter is used to influence the amount of time it takes to perform instance recovery. When the number of dirty buffers exceeds the parameter's value, DBWR will write the dirty buffers to disk. This is known as incremental checkpoint. A smaller value provides shorter instance recovery time but it may cause the DBWR process to become hyperactive, especially in an active database where a large number of buffers are being modified. This in turn may cause excessive write complete waits and a longer db file parallel write time. This parameter is hidden in 9i and DBAs should not have to be concerned with it. Diagnosis

For system-level diagnosis, query the V$SYSTEM_EVENT view to determine if the AVERAGE_WAIT is an issue.

select * from v$system_event where event = 'db file parallel write';

While at the V$SYSTEM_EVENT, look also for the companion events.

select * from v$system_event
where  event in ('write complete waits', 'free buffer waits');

This event occurs in the DBWR. It indicates that the DBWR is performing a parallel write to files and blocks. When the last I/O has gone to disk, the wait ends. Wait Time:

Wait until all of the I/Os are completed.