What are
|
SELECT n.name, SUM(w.p3) Sleeps
FROM v$session_wait w, v$latchname n WHERE w.event = 'latch free' AND w.p2 = n.latch# GROUP BY n.name; |
Following table lists up some latches with additional information:
Latch | SGA Area | Possible Causes | Look For: |
---|---|---|---|
shared pool, library cache | Shared Pool |
|
Sessions (in V$SESSTAT ) with high:
V$SQLAREA / V$SQL ) with:
|
cache buffers lru chain | Buffer Cache LRU lists |
|
Statements with very high LIO/PIO using unselective indexes |
cache buffer chains | Buffer Cache Buffers | Repeated access to a block (or small number of blocks), known as "hot block" |
|
A main cause of shared pool or library cache latch contention is parsing. There are a number of techniques that can be used to identify unnecessary parsing and a number of types of unnecessary parsing:
Unshared SQL
This method identifies similar SQL statements that could be shared if
literals were replaced with bind variables. The idea is to either:
SELECT sqltext FROM v$sqlarea WHERE executions = 1 ORDER BY sql_text; |
SELECT SUBSTR(sql_text,1, 60), COUNT(*)
FROM V$SQLAREA WHERE executions = 1 GROUP BY SUBSTR(sql_text, 1, 60) HAVING COUNT(*) > 1; |
Reparsed Sharable SQL
Check the V$SQLAREA view. Enter the following query:
SELECT sql_text, parse_calls, executions
FROM v$sqlarea ORDER BY parse_calls; |
PARSE_CALLS
value is close to the EXECUTIONS
value for a given statement, you might be continually reparsing that
statement. Tune the statements with the higher numbers of parse calls.
By Session
Identify unnecessary parse calls by identifying the session in which they
occur. It might be that particular batch programs or certain types of
applications do most of the reparsing. To do this, run the following query:
column sid format 99999
column name format a20
SELECT ss.sid, sn.name, ss.value
FROM V$SESSTAT ss, V$STATNAME sn
WHERE name IN ('parse count (hard)','execute count')
AND ss.statistic# = sn.statistic#
AND ss.value > 0
ORDER BY value, sid;
The result is a list of all sessions and the amount of reparsing they do. For
each system identifier (SID), go to V$SESSION to find the name of the program
that causes the reparsing.
Cache buffer LRU chain
The cache buffer 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 bypassing the buffer cache or redesigning the
application.
Cache buffer chains
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 (that is, '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 V$BH
view 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 file#, dbablk, class, state
FROM X$BH
WHERE HLADDR='address of latch';
There are many blocks protected by each latch. One of these buffers will
likely be the hot block. Perform this query a number of times, and identify
the block that consistently appears in the output, using the combination of
file number (file#
) and block number (dbablk
). This
is most likely the hot block. After the hot block has been identified, query
DBA_EXTENTS
using the file number and block number, to identify
the segment.