What are latch free waits?

A latch is a low-level internal lock used by Oracle to protect memory structures. The latch free wait event occurs whenever one Oracle process is requesting a "willing to wait" latch from another process. The event only occurs if the spin_count has been exhausted, and the waiting process goes to sleep.

Latch free waits can occur for a variety of reasons including library cache issues, OS process intervention (processes being put to sleep by the OS, etc.), and so on. One possible cause can also be an oversized shared pool (yes, a bigger large pool not always results in better performance!): Increasing the shared pool allows for a larger number of versions of SQL; this will increase the amount of CPU and latching required for Oracle in order to determine whether a "new" statement is present in the library cache or not.

If you have many latch free waits, you need to further investigate what latches are affected. A good point to start with is Oracle StatsPack which lists them all up in a reasonable order (and you may use my OSPRep Report generator to list them all up ;)

What actions can be taken?

This event should only be a concern if latch waits are a significant portion of the wait time on the system as a whole, or for individual users experiencing problems.

  • To help determine the cause of this wait event, identify the latch(es) contended for. There are many types of latches used for different purposes. For example, the shared pool latch protects certain actions in the shared pool, and the cache buffers LRU chain protects certain actions in the buffer cache.
  • Examine the resource usage for related resources. For example, if the library cache latch is heavily contended for, then examine the hard and soft parse.
  • Examine the SQL statements for the sessions experiencing latch contention to see if there is any commonality.

In the V$SESSION_WAIT view, you find the address of the latch in column P1, the latch number in P2 and the number of times process has already slept in waiting for the latch in P3:
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:

LatchSGA Area Possible CausesLook For:
shared pool, library cacheShared Pool
  • Lack of statement reuse
  • Statements not using bind variables
  • Insufficient size of application cursor cache
  • Cursors closed explicitly after each execution
  • Frequent logon/logoffs
  • Underlying object structure being modified (for example truncate)
  • Shared Pool too small
Sessions (in V$SESSTAT) with high:
  • parse time CPU
  • parse time elapsed
  • ratio of parse count (hard) / execute count
  • ratio of parse count (total) / execute count
Cursors (in V$SQLAREA / V$SQL) with:
  • high ratio of parse calls / executions
  • executions = 1 differing only in literals in the WHERE clause (that is, no bind variables used)
  • high RELOADS
  • high INVALIDATIONS
  • large (> 1 MB) SHARABLE_MEM
cache buffers lru chainBuffer Cache LRU lists
  • Excessive buffer cache throughput. For example, many cache-based sorts, inefficient SQL that accesses incorrect indexes iteratively (large index range scans), or many full table scans
  • DBWR not keeping up with the dirty workload; hence, foreground process spends longer holding the latch looking for a free buffer
  • Cache may be too small
Statements with very high LIO/PIO using unselective indexes
cache buffer chainsBuffer Cache Buffers Repeated access to a block (or small number of blocks), known as "hot block"
  • Sequence number generation code that updates a row in a table to generate the number, rather than using a sequence number generator
  • Identify the segment the hot block belongs to

Shared Pool and Library Cache Latch Contention

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:

  • Manually inspect SQL statements that have only one execution to see whether they are similar:
    SELECT sqltext
      FROM v$sqlarea
     WHERE executions = 1
     ORDER BY sql_text;
  • Or, automate this process by grouping together what may be similar statements. Do this by estimating the number of bytes of a SQL statement which will likely be the same, and group the SQL statements by that many bytes. For example, the example below groups together statements that differ only after the first 60 bytes.
    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;
When the 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.