Wait Events in Oracle Databsae

 1. Buffer Busy wait Event :

can occur when a session is waiting/wanting to access a database block in the buffer cache but it can not as the buffer  is busy. This can occur if either another session is reading the block in to the buffer or another session holds the buffer in an incompatible mode to our request.

Buffer busy waits are common in IO bound storage subsystems.

These waits indicate read/read, read/write  or write/write contention.

The Oracle session is waiting to pin a buffer, a buffer must be pinned before it can be read or modified. 

Only one process can pin a buffer at any one time.

It is also often due to several processes repeatedly reading the same block. (hot blocks )

Actions:

- Eliminate HOT blocks from the application. Check for repeatedly scanned/unselective indexes.

- Try rebuilding the object with a higher PCTFREE so that you reduce the number of rows per block.

- Replace with higher IO subsystesm.

Please note,  in the recent Oracle Db versions we can Ignore PCTUSED, INITRANS, and MAXTRANS:

• PCTUSED: "This parameter is not useful and is ignored for objects with automatic segment-space management."

• INITRANS: "In general, you should not change the INITRANS value from its default."

• MAXTRANS: "This parameter has been deprecated."

PCTFREE is probably the only parameter worth thinking about, and the default of 10 is likely fine.

2. Free buffer waits:

 - Session is waiting for buffer to be available in buffer cache but it's not able to find free buffer because there are too many dirty buffer in the buffer cache. 

 - The main cause of it is either buffer cache is too small or the DBWR is not able to write dirty buffers to disk fast enough. 

 - DBWR is unable to keep up to the write requests.

  - Checkpoint happening too fast  - may be due to high database activity and under-sized online redo log files. 

 - Large sorts and full table scans are filling the buffer cache with modified block faster than the DBWR is able to write to the disk.

 Actions:

 - Analyze the buffer cache and SGA/AMM related parameters and increase SGA/AMM appropriately.

 - Analyze the DBWR slaves and increase it if required.

3. Cache buffer chain latch:

The cache buffers LRU chain latches protect the lists of buffers in the cache. When adding, moving, or removing a buffer from a list, a latch must be obtained to prevent RAM corruption and ensure that only one process handles the list of buffer addresses.

Possible Causes : 

· Processes need to get this latch when they  need to move buffers based on the LRU block replacement policy in the buffer cache

· The cache buffer LRU chain latch is acquired in order to introduce a new block into the buffer cache and when writing a buffer back to disk, specifically when trying  to scan the LRU (least recently used) chain containing all the dirty blocks in the buffer cache. Competition for the cache buffers LRU chain .

· latch is symptomatic of intense buffer cache  activity caused by inefficient SQL  statements. Statements that repeatedly scan large unselective indexes or perform full table scans are the prime culprits. 

· Heavy contention for this latch is generally  due to heavy buffer cache activity which  can be caused, for example, by repeatedly scanning large unselective indexes

Actions :

 Contention in this latch can be avoided implementing multiple buffer pools or increasing the number of LRU latches with the  parameter DB_BLOCK_LRU_LATCHES (The default value is generally  sufficient for most systems).

Its possible to reduce contention for the cache buffer LRU chain latch by increasing the  size of the buffer cache and  thereby reducing the rate at which new blocks are  introduced into the buffer cache.