|
Category :
Database
Resources -> Database Administration -> Wait Events
DB Version
:
Oracle 8i /
Oracle 9i
OS Details :
Sun Solaris9
Reference
: Oracle Metalink
Wait
Event
:
Buffer Busy
This
wait happens when a session wants to access a database block in the
buffer cache but it cannot as the buffer is "busy". The two main cases
where this can occur are when (i) Another session is reading the block
into the buffer and (ii) Another session holds the buffer in an
incompatible mode to our request. Buffer busy waits usually occur on
the database due to contention on one or more of these block types:
data block
segment header block
free list block
undo header
undo block
sort block
save undo block
save undo header
system undo header
system undo block
file header block
extent map
1st level bmb
2nd level bmb
3rd level bmb
bitmap block
bitmap index block
Get details on the event:
Querying the V$SESSION_WAIT view
will give us the following details for this event:
P1 => file#
P2 => block#
P3 => Reason Code
file# => This is the ABSOLUTE file number of the data file that
contains the block that the waiting session wants.
block# => This is the block number in the above file# that the waiting
session wants access to.
Reason Code => Could be either of
these:
| 0 |
A block is being read
|
| 100 |
We want to NEW the
block but the block is currently being read by another session
(most likely for undo). |
| 200 |
We want to NEW the
block but someone else has is using the current copy so we have
to wait for them to finish. |
| 230 |
Trying to get a buffer
in CR/CRX mode , but a modification has started on the buffer
that has not yet been completed. |
| 231 |
CR/CRX scan found the
CURRENT block, but a modification has started on the buffer that
has not yet been completed. |
| 130 |
Block is being read by
another session and no other suitable block image was found, so
we wait until the read is completed. This may also occur after a
buffer cache assumed deadlock. The kernel can't get a buffer in
a certain amount of time and assumes a deadlock. Therefor it
will read the CR version of the block. |
| 110 |
We want the CURRENT
block either shared or exclusive but the Block is being read
into cache by another session, so we have to wait until their
read() is completed. |
| 120 |
We want to get the
block in current mode but someone else is currently reading it
into the cache. Wait for them to complete the read. This occurs
during buffer lookup. |
| 210 |
The session wants the
block in SCUR or XCUR mode. If this is a buffer exchange or the
session is in discrete TX mode, the session waits for the first
time and the second time escalates the block as a deadlock and
so does not show up as waiting very long. In this case the
statistic: "exchange deadlocks" is incremented and we yield the
CPU for the "buffer deadlock" wait event. |
| 220 |
During buffer lookup
for a CURRENT copy of a buffer we have found the buffer but
someone holds it in an incompatible mode so we have to wait.
|
Wait Time => Normal wait time
is 1 second. If the session has been waiting for an exclusive buffer
during the last wait then it waits 3 seconds this wait. The session
will keep timing-out/waiting until it acquires the buffer..
Based on the information about
file# and block# gathered from the V$SESSION_WAIT view we can further
narrow down the tablespace and the segments on which the waits are
happening as follows:
-- To get the tablespace and File
details
SQL> SELECT tablespace_name,
file_name FROM dba_data_files WHERE file_id = <file#>
If the above query did not return
any rows then the wait could be on a tempfile in which case the
following query can be used to identify the tablespace and file
details:
SQL> SELECT tablespace_name,
file_name FROM dba_temp_files f, v$parameter p
WHERE p.name='db_files' AND f.file_id+p.value = <file#>
-- To get the segment details
SQL> SELECT owner ,
segment_name , segment_type, partition_name
FROM dba_extents WHERE file_id = <file#>
AND <block#> BETWEEN block_id AND (block_id +
blocks -1)
Reducing the waits:
Segment header waits:
Increase of number of FREELISTs. Use FREELIST GROUPs
(even in single instance this can make a difference). This
creates additional header blocks for the table to store freelist
information.
Data Block Waits:
Eliminate HOT blocks from the
application. Check for repeatedly scanned / unselective indexes.
Change PCTFREE
and/or PCTUSED. Check for 'right- hand-indexes' (indexes that get
inserted into at the same point by many processes). Increase INITRANS.
Reduce the number of rows per block.
Free Lists blocks:
Add more FREELISTS. In case of
Parallel Server make sure that each instance has its own FREELIST
GROUP(s).
Undo Header:
Add more rollback segments or
starting 9i, use automatic undo.
|
