How to Identify Which Segment is Associated with Buffer Busy Waits (Doc ID 413931.1)

Last updated on JUNE 19, 2017

Applies to:

Oracle Database - Enterprise Edition - Version 8.1.7.4 and later
Information in this document applies to any platform.

Goal

This note shows how to determine which segment is associated with buffer busy waits found in an AWR / statspack report, or 10046 trace. 

It is very important to know which segments are affected by buffer busy waits in order to address the cause for the waits.  This can be accomplished using 10046 traces (with TKProfs) or AWR / statspack output.  The 10046 trace is better than the AWR / Statspack data because it allows you to directly associate the waits with SQL statements causing them and allows you to easily see how the waits affect the execution of that statement.  AWR / statspack reports are still useful in a general way, but you will still not really know precisely which statements caused the waits.

Solution

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms