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
