11gR2 buffer busy waits

11gR2 buffer busy waits
The buffer we want is either being read into the buffer cache by another process, or it is in an incompatible mode and is being modified. Normally the wait time is one second, 
except in the case if this is the second wait for an exclusive block, in which case the wait is three seconds. This event is the indication for data block contention, the same buffer is requested by more than one process at any given time. Reducing waits on this event depends on the block type (data, segment header, undo)

1.query object identifier

SELECT row_wait_obj#,p1,p2,p3
  FROM V$SESSION 
 WHERE EVENT = 'buffer busy waits';

select p1,p2,p3 from v$session_wait_history where event='buffer busy waits';


2.find the segment using file# and block# ,header of file and block

SELECT owner, segment_name, file_id, block_id starting_block_id, block_id + blocks ending_block_id, blocks
FROM dba_extents
WHERE file_id = &file_num AND ( block_id <= &block_id AND (&block_id < (block_id + blocks)));

select owner,segment_name,header_block,header_file from dba_segments where segment_name=$seg_name;


3.The action required depends on the class of block contended for and the actual segment.
(1).segment header
    If the contention is on the segment header, then this is most likely free list contention.
     If possible, switch from manual space management to automatic segment-space management (ASSM).

SELECT SEGMENT_NAME, FREELISTS
  FROM DBA_SEGMENTS
 WHERE SEGMENT_NAME = segment name
   AND SEGMENT_TYPE = segment type;

(2).data block
    Problem: Too many rows in each block
    Solution 1: Reduce the number of rows by changing pctfree/pctused
    Solution 2: Reduce DB_BLOCK_SIZE 
    Problem: Right-hand indexes causing inserts into the same block
    Solution: Use reverse key indexes or rebuild the index

(3).undo header
    Problem: Too many transactions per rollback segment
    Solution 1: Add more rollback segments     Solution 2: Reduce TRANSACTIONS_PER_ROLLBACK_SEGMENT

(4).undo block
    If you are not using automatic undo management, then consider making rollback segment sizes larger.

P3 parameter detail informations before ORACLE 10gR1:
–    A modification is happening on a SCUR or XCUR buffer but has not yet completed.
0    The block is being read into the buffer cache.
100    We want to NEW the block, but the block is currently being read by another session (most likely for undo).
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 its 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 the user to complete the read. This occurs during buffer lookup.
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. Therefore it will read the CR version of the block.
200    We want to NEW the block, but someone else is using the current copy, so we have to wait for that user to finish.
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, 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.
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.