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