Oracle Buffer Busy Wait Event
#LR14217
The buffer busy wait event happens
when a session tries to access a block in the buffer cache but it can't because
the buffer is busy, because another session is modifying the block and the
contents of the block are in flux. To guarantee that the reader has a coherent
image of the block with either all of the changes or none of the changes, the
session modifying the block marks the block header with a flag letting other
users know a change is taking place and to wait until the complete change is
applied.
What
causes the buffer busy wait event
The two main cases where the buffer
busy wait occurs are:
- Another session is reading the block into the buffer -
this specific case has been split out into a separate read by other
session wait event in 10g and higher.
- Another session holds the buffer in an incompatible
mode to our request.
While the block is being changed,
the block is marked as unreadable by others. The changes that are being made
should last under a few hundredths of a second, for example, a disk read should
be under 20 milliseconds and a block modification should be under one
millisecond. Therefore it will take a lot of buffer busy waits to cause a
problem, but some examples of this are:
- Hot block issue, such as the first block on the free
list of a table, with high concurrent inserts. All users will insert into
that block at the same time, until it fills up, then users start inserting
into the next free block on the list, and so on.
- Multiple users running an inefficient SQL statement
performing a full table scan on the same large table at the same time. One
user will read the block off disk, and the other users will wait on buffer
busy waits (or read by other session in 10g and higher) for the physical
I/O to complete.
Getting
more information about buffer busy waits
To get more information about the
SQL statement being executed and the block being waited for, trace the session
or gather data from V$SESSION V$SESSION_WAIT (or just V$SESSION in 10g and
higher):
SELECT
s.sql_hash_value, sw.p1 file#, sw.p2 block#, sw.p3 reason
FROM
v$session_wait sw, v$session s
WHERE
sw.event = 'buffer busy waits'
AND
sw.sid = s.sid
- P1 - file number of the data file containing the block
being waited for
- P2 - block number of the block being waited for
- P3 - this wait is called from many different sections
of Oracle code and each uses their own reason code which differ among
versions
To determine the object being waited
for, use the P1 (file_number) and P2 (block_number) information from the above
query:
SELECT owner , segment_name , segment_type
FROM dba_extents
WHERE file_id = &FileNumber
AND &BlockNumber BETWEEN block_id AND
block_id + blocks -1;
Another query that can be very
useful is finding the objects in the entire Oracle database that are suffering
from "buffer busy waits". The following query gives the top 10
segments:
SELECT
* FROM (
SELECT owner, object_name, subobject_name,
object_type,
tablespace_name, value
FROM v$segment_statistics
WHERE statistic_name='buffer busy waits'
ORDER BY value DESC)
WHERE
ROWNUM <=10
Fixing
buffer busy waits
Once the database object is known,
consider the following causes of contention and their solutions.
- Undo Header
- If using Automatic Undo Management (AUM), increase the size of the undo
tablespace. If not using AUM, add more rollback segments.
- Undo Block
- If using AUM, increase size of the undo tablespace. If not using AUM,
increase rollback segment sizes.
- Data Block-
Data blocks are the blocks that hold the row data in a table or index. The
typical problem is that multiple sessions are requesting a block that is
either not in cache or in an incompatible mode (read by other session in
10g and higher).
- Tune inefficient queries that read too many blocks into the buffer cache.
These queries could flush out blocks that may be useful for other
sessions in the buffer cache. By tuning queries, the number of blocks
that need to be read into the cache is minimized, reducing aging out of
the existing "good" blocks in the cache.
- Resolve Hot Blocks - If the queries above consistently return the same
block or set of blocks, this is considered a hot block scenario. Delete
some of the hot rows and insert them back into the table. Most of the
time, the rows will be placed in a different block. The DBA may need to
adjust PCTFREE and/or PCTUSED to ensure the rows are placed into a
different block. Also talk with the developers to understand why a set of
blocks are hot.
- Place Table in Memory - Cache the table or keep the table in the KEEP POOL.
When multiple sessions are requesting the blocks that reside in the disk,
it takes too much time for a session to read it into the buffer cache.
Other session(s) that need the same block will register 'buffer busy
wait'. If the block is already in buffer cache, however, this possibility
is eliminated. Another alternative is to increase the buffer cache size.
A larger buffer cache means less I/O from disk. This reduces situations
where one session is reading a block from the disk subsystem and other sessions
are waiting for the block.
- Fix Low Cardinality Indexes - Look for ways to reduce the number of low
cardinality indexes, i.e. an index with a low number of unique values
that could result in excessive block reads. This can especially be
problematic when concurrent DML operates on table with low cardinality
indexes and cause contention on a few index blocks.
- Data Segment Header-
Each segment has one header block that contains segment information, e.g.
free and available block details and the highwater mark. At times, this
block can be a point of contention when multiple sessions are attempting
to insert/delete into/from the same table.
- Adjust PCTFREE/PCTUSED or use ASSM - When sessions insert/delete rows into/from a block,
the block must be taken out of the freelist if the PCTFREE threshold
reached. When sessions delete rows from a block, the block will be put
back in the freelist if PCTUSED threshold is reached. If there are a lot
of blocks coming out of the freelist or going into it, all those sessions
have to make that update in the freelist map in the segment header. A
solution to this problem is to create multiple freelists. This will allow
different insert streams to use different freelists and thus update
different freelist maps. This reduces contention on the segment header
block. You should also look into optimizing the PCTUSED/PCTFREE
parameters so that the blocks don't go in and out of the freelists
frequently. Another solution is to use ASSM which avoids the use of
freelists all together.
- Increase Extent Size - If extents are too small, Oracle must constantly
allocate new extents causing contention in the extent map
