Call (800) 766-1884 for Oracle support & training
Free Oracle Tips

Oracle Consulting Support
Oracle Upgrades
Use New Oracle Features
Oracle Replication Support
Oracle Training
Remote Oracle DBA
System Documentation
Oracle Tips
Oracle Performance

Free Oracle Tips



BC Oracle tuning

Oracle training

Oracle support

Remote Oracle




Tools for Tuning I/O Contention

Oracle Tips by Burleson

Once the shared SQL areas and buffer caches have been tuned, the DBA must turn his or her eyes to the I/O performance of the disks and files associated with the Oracle system to realize further performance gains.

Tuning I/O to Avoid Bottlenecks

Once the application and memory areas have been tuned, the next potential performance bottleneck may be the disk subsystem. This system is addressed by tuning the input and output processes that Oracle uses, reducing contention for disk resources, and reducing or eliminating dynamic space allocation within database data files.

Tuning the DBWR Process

The DBWR process manages the buffer cache. In this capacity, it writes filled buffers from the buffer cache in the SGA to the disks. Obviously, a properly tuned DBWR process will be the first step in tuning I/O for the Oracle system. The DBWR process, as described in the section on UTLBSTAT and UTLESTAT, uses the hidden INIT.ORA parameters _DB_BLOCK_WRITE_BATCH and _DB_BLOCK_MAX_SCAN_CNT in Oracle8 to determine when it should write used, or dirty, buffers to the disk, thus freeing them for further use. 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.

Note: In Oracle8i and Oracle, you can no longer tune the parameters mentioned above because they have been deprecated. In Oracle8i and Oracle, the parameter _DB_WRITER_MAX_WRITES controls the maximum number of outstanding I/Os that a database writer can issue, but you should not touch this parameter unless instructed to by Oracle Support.

An example report that pulls the DBWR related statistics from the V$SYSSTAT, V$WAITSTAT, and V$BUFFER_POOL_STATISTICS tables is shown in Source 13.16. The report generated from Source 13.16 is shown in Listing 13.17.

SOURCE 13.16 Report to pull DBWR statistics.

rem dbwr_stat.sql
rem Mike Ault - 11/09/01 Created
col name format a46 heading 'DBWR Statistic'
col value format 9,999,999,999 heading 'Statistic Value'
set pages 40
@title80 'DBWR Statistic Report'
spool rep_out\&db\dbwr_stat
from  (select name, value from v$sysstat
       where name not like '%redo%' and name not like '%remote%') a
where ( like 'DBWR%' or like '%buffer%'
       or like '%write%' or like '%summed%)
select class name, count value from v$waitstat
where class='data block'
select name||' '||to_char(block_size/1024)||'K hit ratio',
  round(((1 - (physical_reads / (db_block_gets + consistent_gets))) * 100),3)
from V$buffer_pool_statistics
select name||' '||to_char(block_size/1024)||'K free buffer wait',free_buffer_wait
from V$buffer_pool_statistics
select name||' '||to_char(block_size/1024)||'K buffer busy wait',buffer_busy_wait
from V$buffer_pool_statistics
select name||' '||to_char(block_size/1024)||'K write complete
wait',write_complete_wait value
from V$buffer_pool_statistics
spool off
set pages 22
ttitle off

LISTING 13.17 Example DBWR statistics report.

Date: 11/09/01                                          Page:   1
Time: 04:44 PM             DBWR Statistic Report          DBAUTIL
                              aultdb1 database 

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

Pay attention to the following in the report in Listing 13.17:

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. In Oracle, increase DB_CACHE_SIZE; in pre-Oracle, increase DB_BLOCK_BUFFERS.

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.

Hit ratios. 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.

There is no need to modify the DBWR internal batch size in Oracle. The write sizes performed by DBWR are automatically calculated by DBWR in Oracle. 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). The values chosen by DBWR are not configurable in Oracle.

DBWR is monitored using the statistic-free buffer waits. The free buffer waits statistic (available from the V$SYSSTAT table) should be as low as possible and should remain at a slowly increasing value. This means that, for your system, you have to decide: If you see spikes when database activity is high, consider using either more DB writers by setting the initialization parameter DB_WRITERS (Oracle7), DB_WRITER_IO_SLAVES (Oracle8) DB_WRITER_PROCESSES and DBWR_IO_SLAVES (Oracle8i, Oracle) equal to the number of disks used by Oracle, if your system doesn’t support asynchronous I/O; or setting the ASYNC_IO initialization parameter to TRUE, if your system supports asynchronous I/O. If setting either the parameter that controls DBWR processes or ASYNC_IO doesn’t help reduce the spikes on free buffer waits , verify that _DB_BLOCK_MAX_SCAN_CNT (Pre-Oracle8i and Oracle)  is set at 30 or greater. Normally, the default value of 30 is fine for this parameter. If you are dissatisfied with the performance of DBWR, first try increasing the INIT.ORA parameter _DB_BLOCK_WRITE_BATCH (on non-Oracle8i, or non-Oracle databases). Increasing this parameter improves DBWR’s ability to use operating system facilities to write to multiple disks and write adjacent blocks in a single I/O operation. Increasing the number of DB block buffers may also be in order if DBWR performance is poor.

On many platforms, Oracle can use the built-in asynchronous ability of the operating system to multithread the DBWR process. On platforms where asynchronous I/O is not possible, multiple DBWR processes should be started using the DB_WRITERS initialization parameter. On some systems, such as HP, it has been shown that multiple DBWR processes may have a positive effect, even with asynchronous I/O set. Note that in Oracle8i, the parameter changed: it is DB_WRITER_PROCESSES; an additional parameter, DBWR_IO_SLAVES, also is used. The difference between processes and I/O slaves is that if an I/O slave terminates, the instance continues to run. You cannot run multiple DBWR processes and DBWR I/O slaves on the same instance. You may need to increase the number of DB_BLOCK_LRU_LATCHES to increase the number of DB_WRITER_PROCESSES. (Note that in Oracle, DB_BLOCK_LRU_LATCHES has changed to _DB_BLOCK_LRU_LATCHES, and becomes undocumented.)

Two additional undocumented parameters that may assist in DBWR tuning are _DB_BLOCK_HASH_BUCKETS and _DB_BLOCK_HASH_LATCHES. The _DB_BLCOK_HASH_BUCKETS parameter should be set to the nearest prime number greater than twice the number of DB_BLOCK_BUFFERS on pre-Oracle systems. In Oracle, this parameter is correctly set, but on older systems, it may be set to twice the value of DB_BLOCK_BUFFERS and will have to be adjusted. The other parameter, _DB_BLOCK_HASH_LATCHES, is set to 1,024, but you may see better performance on some releases of Oracle8i if this is increased to 32,768. The other undocumented parameters dealing with the DBWR and DB blocks should not be touched.

Another way to improve the performance of DBWR on older systems is to enable the checkpoint process by using the CHECKPOINT_PROCESS initialization parameter. This frees DBWR from checkpoint duties, allowing it to concentrate on buffers only. In Oracle8 and later releases, the checkpoint process is automatically started.

See Code Depot for Full Scripts

This is an excerpt from Mike Ault, bestselling author of "Oracle 10g Grid and Real Application Clusters".

You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.

Oracle performance tuning software 

Oracle performance tuning book


Oracle performance Tuning 10g reference poster
Oracle training in Linux commands
Oracle training Excel
Oracle training & performance tuning books



Copyright © 1996 -  2014 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation. 

Hit Counter