• Undo Header - Use Automatic Undo Management (AUM) or add more RBS segments)
  • Undo Block - Use AUM (or increase RBS sizes)
  • Free List - Use ASSM (or freelists groups)
  • Data Block - First determine if it is an I/O problem on version below 10g. In 10g the I/O problem case was given it's own wait event. Sample from v$session_wait by running the following query:.
      select p3, count(*) from v$session_wait where event='buffer busy wait'
    If p3 is > 200 then it is an I/O problem. If it is I/O then either improve I/O performance or change application. Applications running concurrent batch jobs that do full table scans on the same large tables run into this problem. If p3 > 200 or it is Oracle 10g then Look at v$segstat to determine the objects with buffer busy wait.
    • If tables - use Automatic Segment Space Management ASSM (or add freelists)
    • If Indexes - use reverse indexes (warning: these invalidate index range scans) change application to avoid incrementally increasing index keys use hash partitioning or use index partitioning (pre 10i this will require table partitioning) Using reverse key indexes rarely solves the problem. Reverse key indexes usually shift the problem from buffer busy waits to an I/O bottleneck. Reverse key indexes also invalidate index range scans

    Other Arguments for P3:

      1) 120 one user reading the block into the buffer cache, another user wants to make a modification to the block
      2) 130 two or more users want to read the same data block but it is not in the buffer cache. One user will begin the actual IO operation and wait on “db file sequential read” or “db file scattered read”. The other users will wait on “buffer busy wait” of type 130. This buffer busy wait can be alleviated either by caching the data block in the buffer cache or by sequencing users so that they are not reading the same data block at the same time.
      3) 210 – one user is changing and index block and another user wants to make a modification. In this case the index
      4) 220 – this is probably the most common type of buffer busy wait. In the case of tables this indicates that the table needs free lists. In the case of indexes, the possibilities are partitioning the index, indexing on a non sequential key, or reversing the index key. Creating the index with reverse key option has the drawbacks of taking up more space and blocking the possibility of doing index range scans.
      5) 230 – one user is applying rollback to a block and another user wants to read it.
      6) 231 – changes to rollback segment header blocking a reader of the segment header