Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 
 Home
 E-mail Us
 Oracle Articles
New Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

 Applications
 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 
 

db_writer_processes Tips

Oracle Database Tips by Donald BurlesonApril 22, 2013

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

 
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.

 

 

Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


                    









Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


 

Copyright © 1996 -  2017

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.

Remote Emergency Support provided by Conversational

 

 

��  
 
 
Oracle Training at Sea
 
 
 
 
oracle dba poster
 

 
Follow us on Twitter 
 
Oracle performance tuning software 
 
Oracle Linux poster