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 


 

 

 


 

 

 
 

Causes of Oracle Buffer Busy Waits

Oracle Tips by Burleson Consulting
Written December 9, 2003
Updated February 10, 2008

 

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 to a table and index, implementing Automatic Segment Storage Management (ASSM, a.k.a bitmap freelists), and, of course, and adding a missing index to reduce buffer touches.

In order to look at system-wide wait events, we can query the v$system_event performance view. This view, shown below, provides the name of the wait event, the total number of waits and timeouts, the total time waited, and the average wait time per event.

select *
from
   v$system_event
where
   event like ?%wait%?;
 
EVENT                       TOTAL_WAITS TOTAL_TIMEOUTS TIME_WAITED AVERAGE_WAIT
--------------------------- ----------- -------------- ----------- ------------
buffer busy waits                636528           1557      549700   .863591232
write complete waits               1193              0       14799   12.4048617
free buffer waits                  1601              0         622   .388507183

The type of buffer that causes the wait can be queried using the v$waitstat view. This view lists the waits per buffer type for buffer busy waits, where COUNT is the sum of all waits for the class of block, and TIME is the sum of all wait times for that class:
select * from v$waitstat;

CLASS                   COUNT       TIME
 ------------------ ---------- ----------
 data block            1961113    1870278
 segment header          34535     159082
 undo header            233632      86239
 undo block               1886       1706


Buffer busy waits 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.

The v$session_wait performance view, shown below, can give some insight into what is being waited for and why the wait is occurring.

SQL> desc v$session_wait
 
 Name                                      Null?    Type
 ----------------------------------------- -------- ------------
 SID                                                NUMBER
 SEQ#                                               NUMBER
 EVENT                                              VARCHAR2(64)
 P1TEXT                                             VARCHAR2(64)
 P1                                                 NUMBER
 P1RAW                                              RAW(4)
 P2TEXT                                             VARCHAR2(64)
 P2                                                 NUMBER
 P2RAW                                              RAW(4)
 P3TEXT                                             VARCHAR2(64)
 P3                                                 NUMBER
 P3RAW                                              RAW(4)
 WAIT_TIME                                          NUMBER
 SECONDS_IN_WAIT                                    NUMBER
 STATE                                              VARCHAR2(19)


The columns of the v$session_wait view that are of particular interest for a buffer busy wait event are:

  • P1?The absolute file number for the data file involved in the wait.
     
  • P2?The block number within the data file referenced in P1 that is being waited upon.
     
  • P3?The reason code describing why the wait is occurring.


Here's an Oracle data dictionary query for these values:

select
   p1 "File #",
   p2 "Block #",
   p3 "Reason Code"
from
   v$session_wait
where
   event = 'buffer busy waits';


If the output from repeatedly running the above query shows that a block or range of blocks is experiencing waits, the following query should show the name and type of the segment:

select 
   owner,
   segment_name,
   segment_type
from 
   dba_extents
where 
   file_id = &P1
and 
  &P2 between block_id and block_id + blocks -1;


Once the segment is identified, the v$segment_statistics performance view facilitates real-time monitoring of segment-level statistics. This enables a DBA to identify performance problems associated with individual tables or indexes, as shown below.

select
   object_name,
   statistic_name,
   value
from
   V$SEGMENT_STATISTICS
where
   object_name = 'SOURCE$';
 
 
 
OBJECT_NAME   STATISTIC_NAME               VALUE
-----------  -------------------------     ----------
SOURCE$       logical reads                     11216
SOURCE$       buffer busy waits                   210
SOURCE$       db block changes                     32
SOURCE$       physical reads                    10365
SOURCE$       physical writes                       0
SOURCE$       physical reads direct                 0
SOURCE$       physical writes direct                0
SOURCE$       ITL waits                             0
SOURCE$       row lock waits

We can also query the dba_data_files to determine the file_name for the file involved in the wait by using the P1 value from v$session_wait for the file_id.

 
SQL> desc dba_data_files
 
 Name                                      Null?    Type
 ----------------------------------------- -------- -----------
 FILE_NAME                                          VARCHAR2(513)
 FILE_ID                                            NUMBER
 TABLESPACE_NAME                                    VARCHAR2(30)
 BYTES                                              NUMBER
 BLOCKS                                             NUMBER
 STATUS                                             VARCHAR2(9)
 RELATIVE_FNO                                       NUMBER
 AUTOEXTENSIBLE                                     VARCHAR2(3)
 MAXBYTES                                           NUMBER
 MAXBLOCKS                                          NUMBER
 INCREMENT_BY                                       NUMBER
 USER_BYTES                                         NUMBER
 USER_BLOCKS                                        NUMBER


Interrogating the P3 (reason code) value from v$session_wait for a buffer busy wait event will tell us why the session is waiting. The reason codes range from 0 to 300 and can be decoded.
 

Code Reason for wait
- A modification is happening on a SCUR or XCUR buffer but has not yet completed.
0 The block is being read into the buffer cache.
100 We want to NEW the block, but the block is currently being read by another session (most likely for undo).
110 We want the CURRENT block either shared or exclusive but the block is being read into cache by another session, so we have to wait until its read() is completed.
120 We want to get the block in current mode, but someone else is currently reading it into the cache. Wait for the user to complete the read. This occurs during buffer lookup.
130 Block is being read by another session, and no other suitable block image was found, so we wait until the read is completed. This may also occur after a buffer cache assumed deadlock. The kernel can't get a buffer in a certain amount of time and assumes a deadlock. Therefore it will read the CR version of the block.
200 We want to NEW the block, but someone else is using the current copy, so we have to wait for that user to finish.
210 The session wants the block in SCUR or XCUR mode. If this is a buffer exchange or the session is in discrete TX mode, the session waits for the first time and the second time escalates the block as a deadlock, so does not show up as waiting very long. In this case, the statistic: "exchange deadlocks" is incremented, and we yield the CPU for the "buffer deadlock" wait event.
220 During buffer lookup for a CURRENT copy of a buffer, we have found the buffer but someone holds it in an incompatible mode, so we have to wait.
230 Trying to get a buffer in CR/CRX mode, but a modification has started on the buffer that has not yet been completed.
231 CR/CRX scan found the CURRENT block, but a modification has started on the buffer that has not yet been completed.

Reason codes

As I mentioned at the beginning of this article, buffer busy waits are prevalent in I/O-bound systems. I/O contention, resulting in waits for data blocks, is often due to numerous sessions repeatedly reading the same blocks, as when many sessions scan the same index.

In this scenario, session one scans the blocks in the buffer cache quickly, but then a block has to be read from disk. While session one awaits the disk read to complete, other sessions scanning the same index soon catch up to session one and want the same block currently being read from disk. This is where the buffer busy wait occurs?waiting for the buffer blocks that are being read from disk.

The following rules of thumb may be useful for resolving each of the noted contention situations: 

  • Data block contention?Identify and eliminate HOT blocks from the application via changing PCTFREE and or PCTUSED values to reduce the number of rows per data block. Check for repeatedly scanned indexes. Since each transaction updating a block requires a transaction entry, increase the INITRANS value.
     
  • Freelist block contention?Increase the FREELISTS value. Also, when using Parallel Server, be certain that each instance has its own FREELIST GROUPs.
     
  • Segment header contention?Again, increase the number of FREELISTs and use FREELIST GROUPs, which can make a difference even within a single instance.
     
  • Undo header contention?Increase the number of rollback segments.

Mark Bobak notes that buffer busy waits with P3=0 indicate disk I/O contention, indicating the freelists will not improve concurrency:

"buffer busy wait w/ P3=0 means the buffer is locked because the contents are being read from disk by another session. (See MOSC Doc ID 34405.1 for more details.)

This is most likely caused by multiple, concurrent sessions that are reading the same table or set of tables. In my experience, it's most often due to multiple, concurrent queries doing full table scans on the same table.

Since this a read concurrency problem, changing freelists will NOT help."

 In these cases, buffering-up the tables (e.g. KEEP pool), or using faster storage (SSD) can remove this disk enqueue wait evsnts.

Rewards

The identification and resolution of buffer busy waits can be very complex and confusing. Oracle provides the v$segment_statistics view to help monitor buffer busy waits, and the v$system_event views to identify the specific blocks for the buffer busy wait. While identifying and correcting the causes of buffer busy waits is not an intuitive process, the results of your efforts can be quite rewarding.
 
If you like Oracle tuning, you might enjoy my book "Oracle Tuning: The Definitive Reference", with 950 pages of tuning tips and scripts. 

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


 

 

  
 

 
 
 
 
 

 
 
 
 
 
 
Oracle training Excel
 
Oracle performance tuning software 
 

 

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.