Question: I have a
busy database where the DBWR processes are responsible for
the majority of the I/O. How do I know when I need
more db_writer_processes?
Answer: The
db_writer_processes 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.
The default for db_writer_processes
is set by Oracle automatically to 1, or cpu_count/8,
whichever is greater, and the db_writer_processes
cannot exceed a value of 20.
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).
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;
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.
The
db_writer_processes invokes a database writer 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.
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
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
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
