ORACLE LATCH

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 .

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.

What are the Most Popular latch wait event ??

Ø  1. Latch: library cache or Latch: shared pool

Below is Possible causes for above both latch events.

1. Underlying object structure being modified (for example:  truncate)

2. Cursors are closed explicitly after each execution

3. Insufficient size of application cursor cache

4. Statements not using any bind variables

5. Lack of statement reuse

6.Frequent logon/logoffs

7.If  Shared pool is too small

What are Possible suggestion for avoid above both latch events.. ??

1. To Increase SHARED_POOL_SIZE parameter value.

2. Modify Front-end application to use BIND VARIABLE

3. Use CURSOR_SHARING='force' parameter (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. If DBWR not keeping up with the dirty workload; Hence, foreground will process spend longer holding the latch looking for a free buffer

3. Cache may be too small

Possible Suggestion

1. Increase DB_CACHE_SIZE parameter value.

2. Look for: Statements with very high logical I/O or physical I/O, using unselective indexes

 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