Buffer Busy Waits Analysis (vwait.sql)

Use at Your Own Risk

This script is for educational purposes only. Always test in development environments before production use. See our disclaimer for full terms.

What This Script Does

This script analyzes Oracle buffer busy wait statistics by examining different wait classes. Buffer busy waits occur when sessions cannot access database blocks because they are being used by other sessions for reads, writes, or other operations. This helps identify specific types of buffer contention.

Script

rem  vwait.sql
rem
ttitle 'Buffer Busy Waits'
rem
col class format a18 heading 'CLASS'
col count format b999999999 heading 'COUNT'
col time format b999999999 heading 'TIME'
rem
break on report
compute sum of count time on report
rem
select class, count, time
  from v$waitstat
 order by class;

Usage

-- Run the script in SQL*Plus or SQLcl
@vwait.sql

-- No parameters required
-- Shows cumulative statistics since instance startup

Required Privileges

• SELECT on V$WAITSTAT
• Generally available to most database users

Sample Output

Buffer Busy Waits

CLASS                     COUNT       TIME
------------------ ------------ ----------
1st level bmb              234       1234
2nd level bmb               45        234
3rd level bmb               12         67
data block                8901      45678
free list                  567       2345
segment header            1234       5678
sort block                  89        123
system undo block          345       1567
system undo header          23         89
undo block                 789       3456
undo header                156        678
                   ------------ ----------
sum                       12345      61149

Key Output Columns

• CLASS: Type of buffer wait (wait class)
• COUNT: Number of buffer busy wait events
• TIME: Total time waited (in centiseconds)

Understanding Wait Classes

Critical Wait Classes

• data block: User data block contention (most concerning)
• segment header: Space management contention
• free list: Free list management waits (pre-ASSM)
• undo header: Undo segment header contention

Index-Related Waits

• 1st level bmb: Index root block waits
• 2nd level bmb: Index branch block waits
• 3rd level bmb: Index branch block waits (deeper levels)

System Waits

• system undo block: System undo segment blocks
• system undo header: System undo segment headers
• undo block: Regular undo blocks
• undo header: Regular undo segment headers

Performance Analysis

High-Impact Wait Classes

1. data block (>1000 waits):

   • Hot table blocks
   • Concurrent DML on same blocks
   • Small tables with high update rates

2. segment header (>500 waits):

   • Extent allocation contention
   • High insert rates with manual space management
   • Consider ASSM (Automatic Segment Space Management)

3. undo header (>300 waits):

   • Too few undo segments
   • High transaction rates
   • Undo tablespace contention

Troubleshooting Priorities

• Focus on highest COUNT values first
• Consider TIME per wait: TIME/COUNT ratio
• Compare against instance uptime for frequency

Common Use Cases

Performance Bottleneck Analysis

-- During application performance issues
@vwait.sql
-- Look for high data block or segment header waits

Baseline Establishment

-- Capture during normal operations
@vwait.sql
-- Compare with problem periods

Space Management Review

-- Analyze segment header and free list waits
@vwait.sql
-- Consider tablespace management changes

Troubleshooting Strategies

For data block waits

1. Identify hot tables:

   SELECT owner, object_name, object_type
   FROM dba_objects
   WHERE data_object_id IN (
     SELECT obj FROM v$bh
     WHERE tch > 1000  -- high touch count
   );

2. Reduce block contention:

   • Increase PCTFREE to reduce rows per block
   • Partition hot tables
   • Use hash partitioning for even distribution

For segment header waits

1. Enable ASSM for new tablespaces:

   CREATE TABLESPACE ts_name
   EXTENT MANAGEMENT LOCAL
   SEGMENT SPACE MANAGEMENT AUTO;

2. Increase extent sizes to reduce allocations

3. Pre-allocate space for high-growth objects

For undo waits

1. Add more undo segments:

   -- Check current undo segments
   SELECT segment_name, status, bytes/1024/1024 mb
   FROM dba_segments
   WHERE segment_type = 'TYPE2 UNDO';

2. Increase UNDO_TABLESPACE size

3. Tune UNDO_RETENTION parameter

For index waits (bmb)

1. Reverse key indexes for right-hand inserts
2. Hash partitioning for index distribution
3. Increase sequence CACHE size

Advanced Analysis

Calculate Wait Ratios

-- Average wait time per event
SELECT class,
       count,
       time,
       ROUND(time/NULLIF(count,0), 2) avg_wait_time_cs
FROM v$waitstat
WHERE count > 0
ORDER BY count DESC;

Monitor Changes Over Time

-- Take multiple snapshots and compare
-- Look for trending increases in specific wait classes

Related Scripts

• Hot Blocks Analysis - Find specific contended blocks
• Latch Wait Summary - Related latch contention
• Buffer Cache Statistics - Overall buffer cache performance
• Active Session Waits - Current session wait events