Question

Latches and Latch Contention

Answer 1

What is a latch? 

Latches are low level serialization mechanisms used to protect shared data structures in the SGA. A process acquires a latch when working with a structure in the SGA. It continues to hold the latch for the period of time it works with the structure

A latch is a type of a lock that can be very quickly acquired and freed. 
Latches are typically used to prevent more than one process from executing the same piece of code at a given time.

List of latches that are of most concern to a DBA 

BUFFER CACHE LATCHES: 
There are two main latches which protect data blocks in the buffer cache.

- Cache buffers chains latch :- This latch is acquired whenever a block in the buffer cache is accessed. 
Cache Buffers Chains Latch waits are caused by contention where multiple sessions waiting to read the same block.

Reduce logical I/O rates by tuning and minimizing the I/O requirements of the SQL involved.
Identify and reduce the contention for hot blocks.

- Cache buffers LRU chain latch:- This latch is acquired in order to introduce a new block into the buffer cache and when writing a buffer back to disk. 

Reduce contention for this by increasing the size of the buffer cache 

Answer 2

REDOLOG BUFFER LATCHES: 

- Redo allocation latch:- The redo allocation latch is acquired in order to allocate space within the log buffer.

Increase the size of the LOG_BUFFER
Reduce the load of the log buffer using NOLOGGING features when possible. 

- Redo copy latch:-This latch is used to write redo records into the redolog buffer. 

Contention can be reduced by increasing the value of LOG_SIMULTANEOUS_COPIES in multi-cpu system.

 

Answer 3

LIBRARY CACHE:

- Library cache latch:- The library cache latch must be acquired in order to add a new statement to the library cache. - 

Ensure that the application is reusing as much as possible SQL statement.
If the application is already tuned, increase the SHARED_POOL_SIZE.

- Library cache pin latch:- This latch is acquired when a statement in the library cache is reexecuted.

SHARED POOL RELATED LATCHES 

- Shared pool latch: While the library cache latch protects operations withing the library cache, the shared pool latch is used to protect critical operations when allocating and freeing memory in the shared pool. 

Ways to reduce the shared pool latch are, avoid hard parses when possible.

Row cache objects latch:- This latch comes into play when user processes are attempting to access the cached data dictionary values. 

Reduce contention for this latch is by increasing the size of the shared pool (SHARED_POOL_SIZE)