What are
|
Reason | Action |
---|---|
Writes | If it is waiting for writes, then
determine what is delaying the writes and fix it. Check the following:
|
Cache is too small | It is possible DBWR is very
active because of the cache is too small. Investigate whether this is a
probably cause by looking to see if the buffer cache hit ratio is low.
Also use the V$DB_CACHE_ADVICE view to determine whether a
larger cache size would be advantageous. |
Cache is too big | If the cache size is adequate
and the I/O is already evenly spread, then you can potentially modify the
behaviour of DBWR by using asynchronous I/O or by using multiple database
writers. Consider multiple database writer (DBWR) processes or I/O Slaves Configuring multiple database writer processes, or using I/O slaves, is useful when the transaction rates are high or when the buffer cache size is so large that a single DBWn process cannot keep up with the load. DB_WRITER_PROCESSES The DB_WRITER_PROCESSES initialization parameter lets you
configure multiple database writer processes (from DBW0 to DBW9).
Configuring multiple DBWR processes distributes the work required to
identify buffers to be written, and it also distributes the I/O load
over these processes.DBWR_IO_SLAVES If it is not practical to use multiple DBWR processes, then Oracle provides a facility whereby the I/O load can be distributed over multiple slave processes. The DBWR process is the only process that scans the buffer cache LRU list for blocks to be written out. However, the I/O for those blocks is performed by the I/O slaves. The number of I/O slaves is determined by the parameter DBWR_IO_SLAVES . I/O slaves are
also useful when asynchronous I/O is not available, because the multiple
I/O slaves simulate nonblocking, asynchronous requests by freeing DBWR
to continue identifying blocks in the cache to be written.Decide between additional DBWRs and I/O Slaves First you should check whether asynchronous I/O is supported and used by your system. If it is supported but not used, enable asynchronous I/O to see if this alleviates the problem. Using multiple DBWRs parallelizes the gathering and writing of buffers. Therefore, multiple DBWn processes should deliver more throughput than one DBWR process with the same number of I/O slaves. For this reason, the use of I/O slaves has been deprecated in favour of multiple DBWR processes. I/O slaves should only be used if multiple DBWR processes cannot be configured. |
Another possible solution is to adjust the frequency of your checkpoints by
tuning the CHECK_POINT_TIMEOUT
and
CHECK_POINT_INTERVAL
parameters to help the DBWR process to keep
up. Increasing the buffer cache may also be helpful.