Buffer Busy Wait

Buffer Busy Wait 

 This article presents Buffer Busy Wait 

Buffer busy waits could indicate contention in redo, rollback or data blocks. In this action all we need to check the v$waitstat view.

That Buffer busy wait condition happens when:

The block is being read into buffer by another session, And Another session has the  buffer block locked in a mode that is in compatible with the waiting session’s request

The action and resolving this problem

P1: File#  (The waiting data block of datafile numbers. We can see at DBA_DATA_FILES view and  FILE_ID )

P2: Block# (At DBA_EXTEND view block_id)

P3: Class#

So now find the buffer waiting session

SELECT tablespace_name, file_name

    FROM dba_data_files

   WHERE file_id = &v$session.p1;

If you may not see P1 at dba_data_files Then check the Temp files. May e the size of action is big thats why it under temp file

SELECT tablespace_name, file_name

    FROM dba_temp_files f, v$parameter p

   WHERE p.name='db_files'

     AND f.file_id+p.value = &v$session.p1;

Or we can check by Segments

SELECT  owner , segment_name , segment_type

    FROM  dba_extents

   WHERE  file_id = &v$session.p1

     AND  &v$session.p2 BETWEEN block_id AND block_id + blocks -1

Also we can see the block by following select

SELECT *

  FROM v$waitstat

 WHERE COUNT > 0 order by 2 desc;

                   

              So, by following command we can figure out that event.   

SELECT 'Segment Header' class,

       a.segment_type,

       a.segment_name,

       a.partition_name

  FROM dba_segments a, v$session_wait b

 WHERE a.header_file = b.p1

   AND a.header_block = b.p2

   AND b.event = 'buffer busy waits'

UNION ALL

SELECT 'Freelist Groups' class,

       a.segment_type,

       a.segment_name,

       a.partition_name

  FROM dba_segments a, v$session_wait b

 WHERE b.p2 BETWEEN a.header_block + 1

                AND (a.header_block + a.freelist_groups)

   AND a.header_file = b.p1

   AND a.freelist_groups > 1

   AND b.event = 'buffer busy waits'

UNION ALL

SELECT a.segment_type || ' block' class,

       a.segment_type,

       a.segment_name,

       a.partition_name

  FROM dba_extents a, v$session_wait b

 WHERE b.p2 BETWEEN a.block_id AND a.block_id + a.blocks - 1

   AND a.file_id = b.p1

   AND b.event = 'buffer busy waits'

   AND NOT EXISTS

          (SELECT 1

             FROM dba_segments

            WHERE header_file = b.p1 AND header_block = b.p2);