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.
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;
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
Problem: Too many transactions per rollback segment
Solution 1: Add more rollback segments Solution 2: Reduce TRANSACTIONS_PER_ROLLBACK_SEGMENT
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.