Buffer busy wait in Statspack report Oracle
ERROR
On fetching the statspack report from Oracle Database, We are getting the Buffer busy wait on top:
Avg
Total Wait wait Waits
Event Waits Timeouts Time (cs) (ms) /txn
---------------------------- ------------ ---------- ----------- ----- ------
buffer busy waits 225,559 211,961 24,377,029 1081 4.0
enqueue 25,731 21,756 6,786,722 2638 0.5
Parallel Query Idle Wait - S 9,980 7,929 1,762,606 1766 0.2
SQL*Net message from dblink 435,845 0 1,288,965 30 7.7
db file parallel write 4,252 0 1,287,023 3027 0.1
write complete waits 5,872 5,658 581,066 990 0.1
db file sequential read 1,249,981 0 510,793 4 22.0
On Checking the buffer busy waits, we find data block are on top events:
Tot Wait Avg
Class Waits Time (cs) Time (cs)
------------------ ----------- ---------- ---------
data block 216,577 ########## 108 <==
undo header 5,072 609,734 120
undo block 3,770 333,876 89
free list 70 17,426 249
segment header 8 34 4
IDENTIFIED THE CAUSE
Find the blocks id which object causing the problems:
SELECT count(*) NUM_WAITERS, p1 FILE#, p2 BLK#, p3 CLASS
FROM v$session_wait
WHERE event = 'buffer busy waits'
GROUP BY p1, p2, p3
NUM_WAITERS FILE# BLK# CLASS ------------ ----- ------- ------ 92 2 13487 1016 73 2 27762 1016 32 1 29697 1016
Find the Segment name and type which causing problem:
SELECT owner,segment_name,segment_type
FROM dba_extents
WHERE file_id=&file
AND &blockid BETWEEN block_id AND block_id + blocks
NUM_WAITERS OWNER.SEGMENT_NAME TYPE FILE# BLK# CLASS ------------ ------------------- --------- ---- ------- ------ 92 SYSTEM.DEF$_TRANORDER INDEX 2 13487 1016 73 SYSTEM.DEF$_AQCALL TABLE 2 27762 1016 32 SYSTEM.DEF$_TRANORDER INDEX 1 29697 1016
Solution:
Following are the possible solution in buffer cache wait:
1. You should rebuild the index with increase PCTFREE/FREELIST to reduce the contention.
2. You can put this object in keep buffer if its more frequently used and size is small.
3. Check the SQL Queries associated with object which causing contention. Tune the SQL Queries.
4. Increase the Buffer pool size if you can by checking the buffer advisory.
