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


 

HTML Text

BC Oracle tuning

Oracle training

Oracle support

Remote Oracle

 

 

   
 

Buffer Busy Waits and DML Contention
Oracle Tips by Burleson
 

When multiple tasks want to insert or update rows in a table, there may be contention in the segment header for the table. This contention can manifest itself as a buffer busy wait or a freelist wait. Let’s look at some queries that can be run to identify these contention conditions. You are now ready to understand how they occur at the table and index levels.

Oracle keeps a v$ view called v$waitstat and the stats$waitstat table for monitoring wait events. The following query shows how many times Oracle has waited for a freelist to become available. As you can see, it does not tell you which freelists are experiencing the contention problems:

SELECT CLASS, COUNT
FROM V$WAITSTAT
WHERE CLASS = 'free list';

    CLASS                           COUNT
---------------                  ------------
  free list                         383

The main problem with the v$waitstat view and the stats$waitstat table is that they only keep the wait statistics for the whole database and do not distinguish waits by table or index name. Here, you can see that Oracle had to wait 383 times for a table freelist to become available. This could represent a wait of 383 times on the same table or perhaps a single wait for 83 separate tables. While 383 seems to be a large number, remember that Oracle can perform hundreds of I/Os each second, so 383 could be quite insignificant to the overall system. In any case, if you suspect that you know which table’s freelist is having the contention, the table can be exported, dropped, and redefined to have more freelists. While an extra freelist consumes more of Oracle’s memory, additional freelists can help throughput on tables that have lots of insert statements. Generally, you should define extra freelists only on those tables that will have many concurrent update operations.

Using STATSPACK to Find DML Wait Contention

Now let’s look at how STATSPACK can identify these wait conditions for concurrent DML. The stats$waitstat table contains a historical listing of all wait events. The stats$waitstat contain the following classes:

SQL> select distinct class from stats$waitstat

CLASS
------------------
bitmap block
bitmap index block
data block
extent map
free list
save undo block
save undo header
segment header
sort block
system undo block
system undo header
undo block
undo header
unused


rpt_waitstat.sql

set pages 999;
set lines 80;

column mydate heading 'Yr. Mo Dy Hr'     format a13;
column class                              format a20;
column wait_count                         format 999,999;
column time                               format 999,999,999;
column avg_wait_secs                      format 99,999;

break on to_char(snap_time,'yyyy-mm-dd') skip 1;

select
   to_char(snap_time,'yyyy-mm-dd HH24')           mydate,
   e.class,
   e.wait_count - nvl(b.wait_count,0)             wait_count,
   e.time - nvl(b.time,0)                         time
from
   stats$waitstat     b,
   stats$waitstat     e,
   stats$snapshot     sn

where
   e.snap_id = sn.snap_id
and
   b.snap_id = e.snap_id-1
and
   b.class = e.class
and
(
   e.wait_count - b.wait_count  > 1
   or
   e.time - b.time > 1
)
;

Here is a sample report from this query. Here we see a list of all wait events and the object of the wait. This information can sometimes provide insight into a contention problem within Oracle.

Yr. Mo Dy Hr CLASS                WAIT_COUNT         TIME
------------- -------------------- ---------- ------------
2000-12-20 11 data block                    2            0
2000-12-20 12 data block                   21            0
2000-12-20 12 undo header                   5            0
2000-12-20 13 data block                  407            0
2000-12-20 13 segment header                3            0
2000-12-20 13 undo block                  270            0
2000-12-20 13 undo header                  61            0
2000-12-20 16 data block                   55            0
2000-12-20 16 undo block                    8            0
2000-12-20 16 undo header                   5            0
2000-12-20 17 data block                  252            0
2000-12-20 18 data block                  311            0
2000-12-20 18 undo block                  173            0
2000-12-21 00 data block                2,268            0
2000-12-21 00 undo block                  744            0
2000-12-21 00 undo header                 132            0
2000-12-21 01 data block                2,761            0
2000-12-21 01 undo block                1,078            0
2000-12-21 01 undo header                 419            0
2000-12-21 05 data block                    7            0

2000-12-21 09 data block                   17            0
2000-12-21 09 undo block                    8            0
2000-12-21 10 data block                   30            0
2000-12-21 10 undo block                   29            0
2000-12-21 10 undo header                   4            0
2000-12-21 11 data block                  139            0
2000-12-21 11 undo header                   2            0
2000-12-21 12 data block                   17            0
2000-12-21 13 data block                   11            0
2000-12-21 14 data block                   42            0
2000-12-21 14 undo header                   2            0
2000-12-21 15 data block                   10            0
2000-12-21 15 undo block                    5            0
2000-12-21 16 data block                   23            0
2000-12-21 17 data block                   17            0
2000-12-21 17 undo block                    2            0
2000-12-21 18 data block                  122            0
2000-12-21 18 undo block                  117            0
2000-12-21 18 undo header                  19            0
2000-12-21 21 data block                   15            0
2000-12-21 22 data block                    3            0
2000-12-22 02 data block                   59            0

2000-12-22 08 data block                   19            0
2000-12-22 09 data block                   72            0
2000-12-22 09 undo block                    2            0
2000-12-22 10 data block                   57            0
2000-12-22 10 undo block                    7            0
2000-12-22 10 undo header                   3            0
2000-12-22 11 data block                  423            0
2000-12-22 11 undo block                   10            0
2000-12-22 16 data block                    2            0
2000-12-22 17 data block                  319            0
2000-12-22 17 undo block                  149            0
2000-12-22 17 undo header                  44            0
2000-12-22 18 data block                    3            0
2000-12-22 18 undo header                   2            0
2000-12-22 19 data block                   16            0
2000-12-22 20 data block                5,526            0
2000-12-22 20 segment header               30            0
2000-12-22 20 undo block                   46            0

Note that the segment header and data block waits are often related to competing SQL update tasks that have to wait on a single freelist in the segment header.

While this STATSPACK report is useful for summarizing wait conditions within Oracle, it does not tell us the names of the objects that experienced the wait conditions. The following section will show you how to drill down and find the offending data block for buffer busy waits.


This is an excerpt from "Oracle High-Performance SQL Tuning" by Donald K. Burleson, published by Oracle Press.

  
 

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