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

 
 Home
 E-mail Us
 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 


 

 

 


 

 

 
 

Oracle Metric Buffer Busy Waits

Oracle Tips by Burleson Consulting

 

The Buffer Busy Waits Oracle metric occur when an Oracle session needs to access a block in the buffer cache, but cannot because the buffer copy of the data block is locked. This buffer busy wait condition can happen for either of the following reasons:

  • The block is being read into the buffer by another session, so the waiting session must wait for the block read to complete.
     
  • Another session has the buffer block locked in a mode that is incompatible with the waiting session's request.

Because buffer busy waits are due to contention between particular blocks, there's nothing you can do until you know which blocks are in conflict and why the conflicts are occurring. Tuning therefore involves identifying and eliminating the cause of the block contention.

One of the most confounding problems with Oracle is the resolution of buffer busy wait events. Buffer busy waits are common in an I/O-bound Oracle system, as evidenced by any system with read (sequential/scattered) waits in the top-five waits in the Oracle STATSPACK report, like this:

Top 5 Timed Events
                                                          % Total
 Event                         Waits        Time (s)     Ela Time
 --------------------------- ------------ ----------- -----------
 db file sequential read       2,598        7,146           48.54
 db file scattered read       25,519        3,246           22.04

 library cache load lock         673        1,363            9.26
 CPU time                      2,154          934            7.83
 log file parallel write      19,157          837            5.68

The main way to reduce buffer busy waits is to reduce the total I/O on the system. This can be done by tuning the SQL to access rows with fewer block reads (i.e., by adding indexes). Even if we have a huge db_cache_size, we may still see buffer busy waits, and increasing the buffer size won't help.

The resolution of a "buffer busy wait"  events is one of the most confounding problems with Oracle.  In an I/O-bound Oracle system, buffer busy waits are common, as evidenced by any system with read (sequential/scattered) waits in the top-five waits.

Reducing buffer busy waits reduces the total I/O on the system. This can be accomplished by tuning the SQL to access rows with fewer block reads by adding indexes, adjusting the database writer or adding freelists to tables and indexes.  Even if there is a huge db_cache_size , the DBA may still see buffer busy waits and, in this case, increasing the buffer size will not help.

The most common remedies for high buffer busy waits include database writer (DBWR) contention tuning, adding freelists (or ASSM), and adding missing indexes.

This, and many other Oracle performance metrics are discussed in my book "Oracle Tuning" by Rampant TechPress.  You can buy it directly from the publisher and save 30% at this link:

http://www.rampant-books.com/book_1002_oracle_tuning_definitive_reference_2nd_ed.htm


You may also check out this website containing information on fixing the buffer waits busy metric:

http://www.remote-dba.net/oracle_10g_tuning/t_buffer_busy_waits.htm


 

 

��
 
 
 
 

 
 
 

 
Oracle performance tuning software 
 
oracle dba poster
Oracle Linux poster
 
 

 

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 -  2014

All rights reserved by Burleson

Oracle is the registered trademark of Oracle Corporation.