In Oracle8i Database, the db file parallel write wait event parameters according to the V$EVENT_NAME view are files, blocks, and requests. Starting in Oracle9i Database, the parameters are requests, interrupt, and timeout. However, according to event 10046 trace, the first parameter has always been the block count or the DBWR write batch size. In Oracle Database 10g, this wait event falls under the System I/O wait class. Keep the following key thoughts in mind when dealing with the db file parallel write wait event.
The db file parallel write event belongs only to the DBWR process.
A slow DBWR can impact foreground processes.
Significant db file parallel write wait time is most likely an I/O issue.
Common Causes, Diagnosis, and Actions
The DBWR process performs all database writes that go through the SGA. When it is time to write, the DBWR process compiles a set of dirty blocks and issues system write calls to the operating system. The DBWR process looks for blocks to write at various times, including once every three seconds, when posted by a foreground process to make clean buffers, at checkpoints, when the _DB_LARGE_DIRTY_QUEUE, _DB_BLOCK_MAX_DIRTY_TARGET, and FAST_START_MTTR_TARGET thresholds are met, etc.
Although user sessions never experience the db file parallel write wait event, this doesn’t mean they will not be impacted by it. A slow DBWR process can cause foreground sessions to wait on the write complete waits or free buffer waits events. DBWR write performance can be impacted by, among other things, the type of I/O operation (synchronous or asynchronous), storage device (raw partition or cooked file system), database layout, and I/O subsystem configuration. The key database statistics to look at are the systemwide TIME_WAITED and AVERAGE_WAIT of the db file parallel write, free buffer waits, and write complete waits wait events as they are interrelated.
select event, time_waited, average_wait from v$system_event where event in ('db file parallel write','free buffer waits', 'write complete waits');
EVENT TIME_WAITED AVERAGE_WAIT------------------------- ----------- ------------free buffer waits 145448 69.8597502write complete waits 107606 101.228598db file parallel write 4046782 13.2329511
Note Don’t be surprised if the db file parallel write event is absent in your instance. Most likely this is because the DISK_ASYNCH_IO is FALSE. This scenario is normally seen in the HPUX and AIX platforms. Oracle does not consider this to be a bug. However, in the absence of db file parallel write event, Oracle also could not show where the DBWR process is charging its wait time to when waiting for writes to complete. Just because the db file parallel write event is missing, doesn’t mean the DBWR process has no waits.
If the db file parallel write average wait time is greater than 10 centiseconds (or 100ms), this normally indicates slow I/O throughput. You can improve the average wait time in a number of ways. The main one is to use the right kind of I/O operation. If your data files are on raw devices and your platform. supports asynchronous I/O, you should use asynchronous writes. But if your database is on file systems, you should use synchronous writes and direct I/O (this is the operating system direct I/O). There is more discussion on asynchronous and direct I/O later in this section. Besides making sure you are using the right kind of I/O operation, check your database layout and monitor I/O throughput from the operating system using commands such as sar –d or iostat –dxnC. Make sure there is no hot spot.
When the db file parallel write average wait time is high and the system is busy, user sessions may begin to wait on the free buffer waits event. This is because the DBWR process can’t catch up with the demand for free buffers. If the TIME_WAITED of the free buffer waits event is high, you should address the DBWR I/O throughput issue before increasing the number of buffers in the cache. Chapter 7 has more details on the free buffer waits event.
Another repercussion of high db file parallel write average wait time is high TIME_WAITED on the write complete waits wait event. Foreground processes are not allowed to modify the blocks that are in transit to disk. In other words, the blocks that are in the DBWR write batch. They must wait for the blocks to be written and for the DBWR process to clear the bit in the buffer header. The foreground sessions wait on the write complete waits wait event. So the appearance of the write complete waits event is a sure sign of a slow DBWR process. You fix this latency by improving the DBWR I/O throughput.
Note Beginning in Oracle8i Database, a new cloning algorithm clones the current buffers that are in the DBWR write batch. The newly cloned buffers can be modified while the originals become consistent read (CR) buffers and are written to disk. This reduces the write complete waits latency.
select * from v$sysstatwhere name in ('write clones created in foreground', 'write clones created in background');
STATISTIC# NAME CLASS VALUE---------- ----------------------------------- ----- ------- 82 write clones created in foreground 8 241941 83 write clones created in background 8 5417
Note A larger DBWR write batch can also increase the foreground waits on the write complete waits event. This is because the DBWR process will need more time to write a larger batch of blocks, and when this is coupled with poor I/O performance, the result is a high write complete waits. Prior to Oracle8i Database, DBAs tweaked the DB_BLOCK_WRITE_BATCH parameter, which sets the DBWR write batch size. The value can also be seen in the X$KVII view. It is listed as DB writer IO clump. Oracle drastically improved the checkpointing architecture in Oracle8i Database, and DBAs shouldn’t have to mess with the DBWR write batch anymore. Beginning in Oracle8i Database, the write batch size is controlled by the _DB_WRITER_CHUNK_WRITES parameter, and the maximum number of outstanding DBWR I/Os is controlled by the _DB_WRITER_MAX_WRITES parameter. The DBWR write batch size is also revealed by the P1 parameter of the db file parallel write event.
-- Prior to Oracle8i Databaseselect * from x$kvii where kviitag = 'kcbswc';ADDR INDX INST_ID KVIIVAL KVIITAG KVIIDSC-------- ------- -------- -------- ---------- ----------------------------00E33628 6 1 4096 kcbswc DB writer IO clump
-- Beginning in Oracle8i Databaseselect * from x$kvii where kviitag in ('kcbswc','kcbscw');ADDR INDX INST_ID KVIIVAL KVIITAG KVIIDSC-------- ------- -------- -------- ---------- ----------------------------01556808 4 1 4096 kcbswc DBWR max outstanding writes01556818 6 1 204 kcbscw DBWR write chunk
Some initialization parameters can increase DBWR checkpoint activities. While a more active DBWR process is beneficial for reducing the number of free buffer waits events, it will not improve the db file parallel write average wait time if the I/O throughput remains slow. It may not even reduce the write complete waits latency. You still need to improve the DBWR average write time. Check the settings of LOG_CHECKPOINT_TIMEOUT, LOG_CHECKPOINT_INTERVAL, FAST_START_IO_TARGET, _DB_BLOCK_MAX_DIRTY_TARGET, and FAST_START_MTTR_TARGET parameters. Starting in Oracle9i Database, you can see if the target recovery time is causing excessive DBWR checkpoint activity (CKPT_BLOCK_WRITES) by querying the V$INSTANCE_RECOVERY view.
So, how can you improve DBWR average write time?
The quick answer is to turn on asynchronous writes if the hardware supports it; if it doesn’t, use synchronous writes and multiple database writer processes. Unfortunately, enabling asynchronous I/O is not as simple as setting the DISK_ASYNCH_IO parameter to TRUE. This means nothing if the operating system does not support asynchronous I/O. The HP-UX operating system supports asynchronous I/O on raw devices only. The Solaris operating system supports asynchronous I/O on both raw devices and file systems. However, on raw devices, it uses the kernel asynchronous I/O (KAIO) system call, but on file systems, it spawns several light-weight processes (LWP) that make synchronous I/O calls (read(), write(), pwrite(), pread(), pwrite64(), pread64()) to simulate asynchronous I/O. The AIX operating system also supports asynchronous I/O on both raw devices and file systems. On raw devices, asynchronous I/O is handled by the kernel (also known as the fastpath AIO), but on file systems, it is handled by AIO servers through kprocs kernel processes. It is beyond the scope of this book to cover what is and is not supported and how to implement asynchronous I/O, as there are many peculiarities. You can get the details from your system administrator, the system engineer of the hardware vendor, or Oracle Support.
It is important to point out that asynchronous I/O is not always faster and better. Asynchronous I/O operations are unstable on some platforms. Synchronous I/O is more reliable. If you see high TIME_WAITED on the async disk IO wait event in the V$SYSTEM_EVENT view or many AIOWAIT in a truss output, it is a good sign that asynchronous I/O is not working well for you. The async disk IO wait event is instrumented in Oracle9i Database. If your entire database is on file systems, you may get better DBWR I/O performance in synchronous mode. The following shows the db file parallel write AVERAGE_WAIT time with and without asynchronous I/O.
-- database on file systems, disk_asynch_io = trueEVENT AVERAGE_WAIT------------------------------ ------------db file parallel write 12.8993168
-- the same database on file systems, disk_asynch_io = false,-- db_writer_processes = 4EVENT AVERAGE_WAIT------------------------------ ------------db file parallel write .000908619
Following are our recommendations if your database is on file systems. Before you shut down the database to make the changes, make sure you take a snapshot of the V$SYSTEM_EVENT as a baseline.
Set DISK_ASYNCH_IO parameter to FALSE.
If the operating system supports direct I/O to file systems, set the FILESYSTEMIO_OPTIONS parameter to DIRECTIO, otherwise set it to NONE. This parameter is exposed in Oracle9i Database but hidden in Oracle8i Database.
For vxfs (Veritas file system), ask your system administrator to mount the file system with the MINCACHE=DIRECT option. For ufs (Unix file system), mount the file system with the FORCEDIRECTIO option. Check with your system administrator for the specific direct I/O mount option for your file system.
Spawn multiple DBWR processes with the DB_WRITER_PROCESSES parameter.
Note If your redo logs are on raw devices but the datafiles are on file systems, you can set the FILESYSTEMIO_OPTIONS to DIRECTIO and DISK_ASYNCH_IO to TRUE. With this, you can get kernel asynchronous I/O to the raw devices and direct I/O to the file systems.