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
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
SELECT (1 - (Sum(misses) / Sum(gets))) * 100 INTO latch_value FROM v$latch;
DBMS_Output.Put('Latch Hit Ratio : ' || Format(latch_value));
Note: The ratio should be as close as possible to 1. If the latch hit ratio is not close to 1, then you may require further anaylsis
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.
Latches Types
Buffer Cache Latches
Latches are typically used to prevent more than one process from executing the same piece of code at a given time.
Latches Types
Buffer Cache Latches
- 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
Redo-log 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 OG_SIMULTANEOUS_COPIES in multi-cpu system.
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 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)