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.
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. Formally:
least(20, greatest(1, cpu_count/8))
Note: The
db_writer_processes should normally be calculated by Oracle.
There are only very rare cases where you would want to change the
value of db_writer_processes.
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
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.
|
|
Get the Complete
Oracle SQL Tuning Information
The landmark book
"Advanced Oracle
SQL Tuning The Definitive Reference" is
filled with valuable information on Oracle SQL Tuning.
This book includes scripts and tools to hypercharge Oracle 11g
performance and you can
buy it
for 30% off directly from the publisher.
|