Estadisticas db_writer

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

 

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.