buffer busy waits

The buffer busy waits event is related to read/read, read/write, and write/write contention.

1. Establish the name of the object that the buffer lock contention is for using the P1 and P2 values supplied by the CURRENT_EVENT cursor:

2.           select segment_name, partition_name

from   dba_extents

where   between block_id and (block_id + blocks - 1)

and    file_id    = ;

3. Find out the type of block using the P1 and P2 values provided by the CURRENT_EVENT cursor. The contention may be for a segment header block, freelist group block, or data block. In Oracle Database 10g, this information is provided by the P3 parameter. See the “buffer busy waits” section in Chapter 6 for more details.

select segment_type || ' header block'

from   dba_segments 

where  header_file  = 

and    header_block = 

union all

select segment_type || ' freelist group block'

from   dba_segments

where  header_file     = 

and     between header_block + 1 and 

                          (header_block + freelist_groups)

and    freelist_groups > 1

union all

select segment_type || ' data block'

from   dba_extents

where   between block_id and (block_id + blocks - 1)

and    file_id    = 

and    not exists (select 1 

                   from   dba_segments 

                   where  header_file  = 

                   and    header_block = );

4. Obtain the SQL statement that is associated with the buffer busy waits event using the hash value supplied by the CURRENT_EVENT cursor:

select hash_value, address, piece, sql_text

from   v$sqltext 

where  hash_value = 

order by piece;