Is there any way to avoid buffer busy wait?

• Buffer busy waits occur when an Oracle session needs to access a block in the buffer cache, but cannot because the buffer copy of the data block is locked. This buffer busy wait condition can happen if the block is being read into the buffer by another session, so the waiting session must wait for the block read to complete or another session has the buffer block locked in a mode that is incompatible with the waiting session's request.

• The main way to reduce buffer busy waits is to reduce the total I/O on the system. This can be done by tuning the SQL to access rows with fewer block reads (i.e., by adding indexes). Even if we have a huge db_cache_size, we may still see buffer busy waits, and increasing the buffer size won't help.

• What's the nature of the queries encountering the buffer busy wait? What kind of segment did you identify? Table? Index? If index, what block? root block? first level branch block?

• When I see lots of buffer busy waits, I look at the SQL encountering the wait, see if there are opportunities to reduce concurrency, and/or tune the SQL.

• Cache the table or keep the table in the KEEP POOL.  When multiple sessions are requesting the blocks that reside in the disk, it takes too much time for a session to read it into the buffer cache.  Other session(s) that need the same block will register 'buffer busy wait'.  If the block is already in buffer cache, however, this possibility is eliminated.  Another alternative is to increase the buffer cache size.  A larger buffer cache means less I/O from disk.  This reduces situations where one session is reading a block from the disk subsystem and other sessions are waiting for the block.

• Look for ways to reduce the number of low cardinality indexes, i.e. an index with a low number of unique values that could result in excessive block reads. This can especially be problematic when concurrent DML operates on table with low cardinality indexes and cause contention on a few index blocks.

• The most common remedies for high buffer busy waits include database writer (DBWR) contention tuning, adding freelists to a table and index, implementing Automatic Segment Storage Management (ASSM, a.k.a bitmap freelists), and, of course, and adding a missing index to reduce buffer touches. 

• Tune inefficient queries that read too many blocks into the buffer cache.  These queries could flush out blocks that may be useful for other sessions in the buffer cache. By tuning queries, the number of blocks that need to be read into the cache is minimized, reducing aging out of the existing "good" blocks in the cache. 

• The v$session_wait performance view, can give some insight into what is being waited for and why the wait is occurring.

• When sessions insert/delete rows into/from a block, the block must be taken out of the freelist if the PCTFREE threshold reached.  When sessions delete rows from a block, the block will be put back in the freelist if PCTUSED threshold is reached.  If there are a lot of blocks coming out of the freelist or going into it, all those sessions have to make that update in the freelist map in the segment header.  A solution to this problem is to create multiple freelists.  This will allow different insert streams to use different freelists and thus update different freelist maps.  This reduces contention on the segment header block.  You should also look into optimizing the PCTUSED/PCTFREE parameters so that the blocks don't go in and out of the freelists frequently. Another solution is to use ASSM which avoids the use of freelists all together.

• If extents are too small, Oracle must constantly allocate new extents causing contention in the extent map