Buffer Busy Waits上个经常碰到的等待事件,看到有篇文章对此讲述的比较详细,转一下...
原文地址 http://www.confio.com/English/Tips/Buffer_Busy_Waits.php
Buffer Busy Waits
Definition
This wait happens when
a session tries to access a block in the buffer cache but it cannot because the
buffer is busy, i.e. 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.
The two main cases where
this wait occurs are:
§ Another session is reading the block into the
buffer – this specific case has been split out into a “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, e.g. 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
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 and 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 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