Thursday, February 19, 2009

buffer busy waits

The buffer busy waits event occurs when a session wants to access a data block in the buffer cache that is currently in use by some other session.
The other session is either reading the same data block into the buffer cache from the datafile, or it is modifying the one in the buffer cache.
In order to guarantee that the reader session 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 sessions know that a change is taking place and to wait until the complete change is applied.

Use the script posted in "Identify waiting sessions".

From that, you can:

1. Using the SQL hash value from the CURRENT_EVENT cursor, you can capture the SQL text from the GV$SQLTEXT view as shown next and store it in the appropriate history table.
If you are also interested in the SQL statistics, you can get the information from the V$SQLAREA using the same SQL hash value from the cursor:
SELECT INST_ID,
         HASH_VALUE,
         ADDRESS,
         PIECE,
         SQL_TEXT
    FROM GV$SQLTEXT
   WHERE HASH_VALUE = 4190307765                              --sql_hash_value
                                AND INST_ID = 1                 --RAC Instance
ORDER BY INST_ID, PIECE;

2. Find the segment, which the responsible data block belongs to:
SELECT OWNER, SEGMENT_NAME, SEGMENT_TYPE
  FROM DBA_EXTENTS
 WHERE FILE_ID = &P1 AND &P2 BETWEEN BLOCK_ID AND BLOCK_ID + BLOCKS - 1;

3. Find the datafile, where the above segment is stored:
SELECT *
  FROM DBA_DATA_FILES
 WHERE FILE_ID = &P1;

4. For databases prior to 10gR1, P3 indicatew the reason for the wait. These are:
100: The blocking session is reading the block into cache, most likely the undo block for rollback; the waiting session wants exclusive access to create a new block for this information.
110: The blocked or waiting session wants to access the current image of the block in either shared (to read) or exclusive (to write) mode, but the blocking session is reading the block into cache.
120: The blocked session wants to access the block in current mode; the blocking session is reading the block in the cache. This happens during buffer lookups.
130: One or more sessions want to access the same block, but it is not in the buffer.
One session will perform the I/O operation and post either a "db file sequential read" or a "db file scattered read" event,while the waiting sessions will post buffer busy waits with this reason code.
200:The blocking session is modifying the block in the cache; the waiting session wants exclusive access to create a new block.
210:The blocking session is modifying the block, while the blocked session wants the current version of the block in exclusive mode. This happens when two processes want to update the same block.
220:The blocking session is modifying the block, while the blocked session wants to access the block in current mode during buffer lookup.
230: The blocking session is modifying the block, while the blocked session wants shared access of a coherent version of the block.
231:The blocking session is modifying the block, while the blocked session is the reading current version of the block when shared access of a coherent version of the block was wanted.

No comments:

Post a Comment