Oracle Latch

My Signature Article

Tips:
Oracle Certification - Tips
DBA Preparation - Tips
Practical DBA - Tips

What is Latch ?

A mechanism to protect shared data structures in the System Global Area.
For Example: latches protect the list of users currently accessing the database and protect the data structures describing the blocks in the buffer cache.

A server or background process acquires a latch for a very short time while manipulating or looking at one of these structures.


During DB performance we will see LATCH event ...so what is latch event and how many types of latch events ?

A latch is a low-level internal lock used by Oracle to protect memory structures.

The latch free event is updated when a server process attempts to get a latch, and the latch is unavailable on the first attempt.

Most Popular latch wait event are ...

1. Latch: library cache or Latch: shared pool

Below is Possible causes for above both latch events.

1. Lack of statement reuse
2. Statements not using bind variables
3. Insufficient size of application cursor cache
4. Cursors closed explicitly after each execution
5. Frequent logon/logoffs
6. Underlying object structure being modified (for example truncate)
7. Shared pool too small

Below is Possible suggestion for aviod above both latch events.

1. Increase SHARED_POOL_SIZE parameter value.
2. Modify Frontend application to use BIND VARIABLE
3. Use CURSOR_SHARING='force' (for temporary basis)


2. Latch: cache buffers lru chain

Possible Causes

1. Inefficient SQL that accesses incorrect indexes iteratively (large index range scans) or many full table scans.
2. DBWR not keeping up with the dirty workload; hence, foreground process spends longer holding the latch looking for a free buffer
3. Cache may be too small

Possible Suggestion

1. Look for: Statements with very high logical I/O or physical I/O, using unselective indexes
2. Increase DB_CACHE_SIZE parameter value.
3. 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.

For symmetric multiprocessor (SMP) systems, Oracle automatically sets the number of LRU latches to a value equal to one half the number of CPUs on the system. For non-SMP systems, one LRU latch is sufficient.

Contention for the LRU latch can impede performance on SMP machines with a large number of CPUs. LRU latch contention is detected by querying V$LATCH, V$SESSION_EVENT, and V$SYSTEM_EVENT. To avoid contention, consider tuning the application, bypassing the buffer cache for DSS jobs, or redesigning the application.


Latch: cache buffers chains

Possible Causes

1. Repeated access to a block (or small number of blocks), known as a hot block
2. From AskTom:

Contention for these latches can be caused by:

- Very long buffer chains.
- very very heavy access to the same blocks.

Possible Suggestion

1. From AskTom:
When I see this, I try to see what SQL the waiters are trying to execute. Many times,
what I find, is they are all running the same query for the same data (hot blocks). If
you find such a query -- typically it indicates a query that might need to be tuned (to
access less blocks hence avoiding the collisions).

If it is long buffer chains, you can use multiple buffer pools to spread things out. You
can use DB_BLOCK_LRU_LATCHES to increase the number of latches. You can use both
together.

The cache buffers chains latches are used to protect a buffer list in the buffer cache. These latches are used when searching for, adding, or removing a buffer from the buffer cache. Contention on this latch usually means that there is a block that is greatly contended for (known as a hot block).

To identify the heavily accessed buffer chain, and hence the contended for block, look at latch statistics for the cache buffers chains latches using the view V$LATCH_CHILDREN. If there is a specific cache buffers chains child latch that has many more GETS, MISSES, and SLEEPS when compared with the other child latches, then this is the contended for child latch.

This latch has a memory address, identified by the ADDR column. Use the value in the ADDR column joined with the X$BH table to identify the blocks protected by this latch. For example, given the address (V$LATCH_CHILDREN.ADDR) of a heavily contended latch, this queries the file and block numbers:

SELECT OBJ data_object_id, FILE#, DBABLK,CLASS, STATE, TCH
FROM X$BH
WHERE HLADDR = 'address of latch'
ORDER BY TCH;

X$BH.TCH is a touch count for the buffer. A high value for X$BH.TCH indicates a hot block.

Many blocks are protected by each latch. One of these buffers will probably be the hot block. Any block with a high TCH value is a potential hot block. Perform this query a number of times, and identify the block that consistently appears in the output. After you have identified the hot block, query DBA_EXTENTS using the file number and block number, to identify the segment.

After you have identified the hot block, you can identify the segment it belongs to with the following query:

SELECT OBJECT_NAME, SUBOBJECT_NAME
FROM DBA_OBJECTS
WHERE DATA_OBJECT_ID = &obj;

In the query, &obj is the value of the OBJ column in the previous query on X$BH.


5. Latch: row cache objects

The row cache objects latches protect the data dictionary.
Suggestion: Increase SHARED_POOL_SIZE parameter to avoid this latch.