Many thanks to Rahul, a reader who has posed a concern regarding whether or not DBWn writes to disk every three seconds. When this question was posted to
"AskTom", the followup stated:
dbwr can be controlled by using the log_checkpoint_* parameters, fast_start_*
parameters, (or the max dirty target in older releases)
Amar offers the following arguments to support his opinion:
- As per note 147468.1 on metalink:
- Oracle writes the dirty buffers to disk only on certain conditions:
- A shadow process must scan more than one-quarter of the db_block_buffer parameter.
- Every three seconds.
- When a checkpoint is produced.
Dirty buffers flushing is done by DWBR process, hence my opinion on the article.
- As per Nitin Vengurlekar note on metalink (91062.1, very old one but good insight)
- DBWR write dirty buffers when idle (indx value 9)
DBWR is set to timeout after three seconds of inactivity. Each timeout will awaken DBWR to traverse through the buffer headers (scan size equals 2*_db_block_write_batch) to find and write out any current or dirty blocks (temporary, a.k.a. sort blocks, are skipped) . If there are any buffers in the dirty list, then this is also considered non-idle activity. This prevents DBWR from being too idle.
Jonathan Lewis note :
- "Oracle decided to keep trickling dirty blocks to disc at a higher rate than had been effected by the old 3-second idle write rate (every 3 seconds, dbwr wakes up and writes a few blocks to disc if it has had no other work in the interval)." Extract from http://www.jlcomp.demon.co.uk/faq/log_checkpoint.html
Jonathan also points that new parameters have also been introduced to control the dirty blocks writing rate.
- Another article on dbasupport.com by David Nishimoto
SQL Tuning - File I/O Performance
"The Database Writer (DBWR) writes data from the buffer cache into the data files. Every three seconds DBWR wakes up to check the dirty list for blocks to write. "
- More from Jonathan Lewis:
- I've just run a quick test on 22.214.171.124 and 126.96.36.199, and when the system is
idle (but has a number of dirty buffers), DBWR still wakes up every three
seconds on a "background timeouts" event. But this does not necessarily
result in some of the dirty buffers being written to disk.
- Metalink note 91062.1 is now out of date. DBWR doesn't need
to scan the buffer headers the way it used to as (from 8.1) the
buffers are put on to a checkpoint queue as they are first made dirty.