buffer busy waits: Wait: contention for a buffer already being read or modified by another session
OracleWhat does it mean?
A session is waiting to access a buffer block that another session is reading or modifying at the same time. This is in-memory block contention.
Why does it happen?
Concentration on hot blocks, contention on the right edge of indexes with monotonically increasing keys, insufficient freelists/non-ASSM segments, or heavy concurrent DML on the same block.
How to fix it
Find which segment and block class are affected (v$session, v$segment_statistics, ASH). For hot index blocks, consider a reverse key or hash-partitioned index. Use ASSM (Automatic Segment Space Management). Under high concurrency, increase INITRANS/PCTFREE. Spread the application access pattern.
More from this engine
- log file syncWait: session waiting for LGWR to flush redo to disk on commit
- db file sequential readWait: single-block physical read, typically an index or table lookup
- db file scattered readWait: multi-block physical read, typically a full table or index fast full scan
- gc buffer busyWait (RAC): contention for a cache-fusion block being transferred between instances
- library cache lockWait: contention on a library cache object handle (parse/DDL serialization)
- ORA-00604error occurred at recursive SQL level
- ORA-01438value larger than specified precision allowed for this column