Question:  I suspect that I’m having a problem tuning my database writer process.  I understand that free buffer waits are evidence of a DBWR problem, but I also wonder what else signifies a need to increase the number of DBWR processes (by adjusting the db_writer_processes or dbwr_io_slaves parameter)?

 Answer:  The DBWR database writers manage the “dirty block” cleanouts from the data buffer, and there are very few tuning option other than adjusting the number of DBWR processes. 

 Oracle allows for multiple DBWR processes and I/O slaves as possible tuning solutions to prevent the database writer from becoming a bottleneck. These are the main tuning knobs for the DBWR processes (db_writer_processes, dbwr_io_slaves disk_ascych_io) and from Oracle7, the deprecated db_writers parameter.

The most common symptom of a DBWR tuning issue is high “free buffer waits” and “write complete waits” in the AWR table dba_hist_system_event:

 select
  event,
  total_waits,
  time_waited,
  average_wait
from
  dba_hist_system_event
where
  event like 'db file %' or
  event = 'free buffer waits' or
  event = 'write complete waits'
order by
  time_waited desc;

 This query will show periods where high free buffer waits may indicate a DBWR bottleneck.  You can also use the stats$system_event table to monitor “free buffer waits” if you do not have the licenses for AWR.

 There are a few hidden parameters relating to the database writer (_db_writer_verify_writes, db_block_checksum, _db_block_write_batch and _db_block_max_scan_cnt and _db_writer_scan_depth) but these DBWR parameters should only be changed at the request of Oracle technical support.

There are also several metrics that can be used for tuning the DBWR:

 select distinct name from v$sysstat where name like 'DBWR%'

NAME
----------------------------------------------------------------
DBWR buffers scanned
DBWR checkpoint buffers written
DBWR checkpoints
DBWR cross instance writes
DBWR free buffers found
DBWR fusion writes
DBWR lru scans
DBWR make free requests
DBWR revisited being-written buffer
DBWR summed scan depth
DBWR transaction table writes
DBWR undo block writes

See here for additional internals on tuning the DBWR processes.  DBWR triggers on the following conditions:

 1. A user process writes a used buffer to the dirty buffer list and finds it is _db_block_write_batch / 2 long.

2. A user process searches _db_block_max_scan_cnt buffers without finding a clean one.

3. The  DBWR has been inactive for three seconds.

4. When a checkpoint occurs, LGWR signals DBWR to trigger it to write.

 The DBWR writes out _db_block_write_batch buffers each time it is triggered. If there aren't that many buffers in the dirty buffer list, the buffers on the LRU list are written until _db_block_write_batch buffers are written.

 An example report that pulls the DBWR related statistics from the v$sysstat, v$waitstat, and v$buffer_pool_statistics views are shown here:

 rem dbwr_stat.sql
rem mike ault - 11/09/01 created
rem
col name format a46 heading 'dbwr statistic'
col value format 9,999,999,999 heading 'statistic value'
set pages 40
@title80 'dbwr statistic report'
spool rep_out\&db\dbwr_stat
select a.name,a.value
from  (select name, value from v$sysstat
       where name not like '%redo%' and name not like '%remote%') a
where (a.name like 'dbwr%' or a.name like '%buffer%'
       or a.name like '%write%' or a.name like '%summed%)
union
select class name, count value from v$waitstat
where class='data block'
union
select name||' '||to_char(block_size/1024)||'k hit ratio',
  round(((1 - (physical_reads / (db_block_gets + consistent_gets))) * 100),3)
value
from v$buffer_pool_statistics
union
select name||' '||to_char(block_size/1024)||'k free buffer wait',free_buffer_wait
value
from v$buffer_pool_statistics
union
select name||' '||to_char(block_size/1024)||'k buffer busy wait',buffer_busy_wait
value
from v$buffer_pool_statistics
union
select name||' '||to_char(block_size/1024)||'k write complete
wait',write_complete_wait value
from v$buffer_pool_statistics
/
spool off

DBWR Statistic                                 Statistic Value
---------------------------------------------- ---------------
DBWR buffers scanned                                         0
DBWR checkpoint buffers written                          2,601
DBWR checkpoints                                            18
DBWR cross instance writes                                   0
DBWR free buffers found                                      0
DBWR fusion writes                                           0
DBWR lru scans                                               0
DBWR make free requests                                      0
DBWR revisited being-written buffer                          0
DBWR summed scan depth                                       0
DBWR transaction table writes                               95
DBWR undo block writes                                   1,156
DEFAULT 2K buffer busy wait                                  0
DEFAULT 2K free buffer wait                                  0
DEFAULT 2K hit ratio                                        98
DEFAULT 2K write complete wait                               0
DEFAULT 8K buffer busy wait                                  3
DEFAULT 8K free buffer wait                                  0
DEFAULT 8K hit ratio                                        99
DEFAULT 8K write complete wait                               0
buffer is not pinned count                             570,196
buffer is pinned count                                 392,710
change write time                                          340
commit cleanout failures: buffer being written               0
commit cleanout failures: write disabled                     0
data block                                                   3
dirty buffers inspected                                      0
free buffer inspected                                        0
free buffer requested                                    5,054
hot buffers moved to head of LRU                             0
no buffer to keep pinned count                         208,657
physical writes                                          4,792
physical writes direct                                   2,056
physical writes direct (lob)                                 0
physical writes non checkpoint                           3,476
pinned buffers inspected                                     0
summed dirty queue length                                  122
switch current to new buffer                               219
write clones created in background                           4
write clones created in foreground                           7

 The most import of these DBWR metrics includes:

• DBWR checkpoints. Number of checkpoint requests sent to DBWR since startup.

• DBWR buffers scanned. Number of DB buffers scanned since startup.

• Summed dirty queue length. Length of the dirty buffer queue. If this gets over 50, Oracle says to add DB_WRITER_PROCESSES.

• Physical writes. Number of physical writes performed by the DBWR. If this is high, then there may be insufficient buffers allocated. (increase db_cache_size)

• Data block. A statistic harvested from the v$waitstat table; shows if there are any data block waits occurring. Excessive data block waits when the hit ratio is high can indicate need for more DBWR processes.

• DEFAULT 8K hit ratio   - A hit ratio will be calculated for each buffer pool and each separate block size in the default pool. Generally speaking, high hit ratios are desirable, low are not; but hit ratio is not the end-all/be-all statistics for buffer health.

• Waits. Various waits will be reported for all pools and all areas of the default buffer with different block sizes. Pay attention to waits that deal with writes; if write-type waits are excessive, then more DBWR processes are in order. Buffer busy waits may indicate a need for more buffers.

   

Also, note that the DBWR undo block writes Oracle metric is the number of transaction table blocks written by DBWR. It is an indication of how many "hot" buffers were written, leading to write complete waits.

 There is no need to modify the DBWR internal batch size, and the write size depends on the number of dirty blocks to be written, and is tempered with the maximum number of writes (which is operating system-specific).

 In a nutshell, DBWR tuning involves monitoring for free buffer waits and adjusting the db_writers parameter to accommodate peak DML periods.

If you like Oracle tuning, check-out my latest book "Oracle Tuning: The Definitive Reference", the best deal at 30% off, buying directly from the publisher.  Packed with almost 1,000 pages of Oracle performance tuning techniques, it's the foolproof way to find and correct Oracle bottlenecks.