A Oracle DBA's BLOG

Welcome to my ORACLE DBA blog. You will be Amazing!!!

Wednesday, October 2, 2013

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);

No comments:

Post a Comment