Oracle Latch Waits - Diagnose Shared Pool & Buffer Latches

Oracle Latch Waits

Overview

Wait Event Class: Concurrency

Key Events: latch free, latch: shared pool, latch: cache buffers chains, latch: library cache, latch: row cache objects

Latches are lightweight, low-level serialization mechanisms that protect Oracle’s in-memory data structures. Unlike enqueues (which queue waiting sessions fairly), latches use a spin-then-sleep approach:

1. A session needing a latch attempts to acquire it in a tight spin loop
2. After spinning for _spin_count iterations (default ~2000), the session sleeps and tries again
3. After enough failed attempts, the session records a latch free or named latch wait

Latches are acquired and released very rapidly — typical hold times are microseconds. They are designed for structures that change frequently and need protection across very short critical sections. Because they are not fair (no FIFO queue), a highly contended latch causes many sessions to spin wasting CPU while waiting.

Important Latch Types

Latch Name	Protects	Common Cause of Contention
shared pool	Shared pool memory allocation	Hard parsing, shared pool fragmentation
library cache	Library cache (parsed cursors)	Hard parsing storms, cursor invalidation
library cache pin	Pinned library cache objects	DDL during active DML, package compilation
cache buffers chains	Buffer cache hash chains	Hot blocks accessed by many sessions
cache buffers lru chain	LRU list for buffer cache	Cache under pressure, excessive physical reads
row cache objects	Data dictionary (row cache)	Heavy data dictionary access
redo allocation	Redo buffer space allocation	Extreme redo generation rates

latch free vs Named Latch Events

In Oracle 10g and earlier, all latch waits recorded as latch free. From 10g onward, Oracle introduced named latch events (latch: shared pool, latch: cache buffers chains, etc.) for the most common latches. Both forms appear in AWR. Named latch events provide more specific diagnostic information.

---

When This Wait Is a Problem

Thresholds

Metric	Assessment
Latch hit ratio > 99.9%	Good — low contention
Latch hit ratio 99–99.9%	Monitor — some contention
Latch hit ratio < 99%	Problem — significant spinning
latch free in AWR Top 5 wait events	Investigate immediately
Specific latch with sleeps > 1% of gets	Hot latch — targeted action needed

Normal vs. Problematic

Latch waits are expected at very low levels (< 1 in 1000 gets). They become a problem when:

• A single latch becomes a bottleneck serializing many sessions
• CPU utilization is high but throughput is low (sessions spinning on latches)
• AWR shows latch waits as a significant fraction of non-idle DB time
• The same latch child appears consistently as the hot latch

---

Diagnostic Queries

1. Top Latches by Misses and Sleeps

-- Most contended latches at the instance level
SELECT
    name,
    gets,
    misses,
    sleeps,
    immediate_gets,
    immediate_misses,
    ROUND(misses * 100.0 / NULLIF(gets, 0), 4)           AS miss_pct,
    ROUND(sleeps * 100.0 / NULLIF(gets, 0), 4)           AS sleep_pct,
    spin_gets,
    wait_time / 100                                        AS wait_secs
FROM v$latch
WHERE misses > 0
ORDER BY sleeps DESC
FETCH FIRST 20 ROWS ONLY;

2. Hot Latch Children — Find the Specific Contention Point

-- For latches with children (like cache buffers chains), identify the hot child
SELECT
    lc.name,
    lc.child#,
    lc.addr,
    lc.gets,
    lc.misses,
    lc.sleeps,
    ROUND(lc.sleeps * 100.0 / NULLIF(lc.gets, 0), 4) AS sleep_pct
FROM v$latch_children lc
WHERE lc.name IN ('cache buffers chains', 'library cache', 'shared pool')
  AND lc.sleeps > 0
ORDER BY lc.sleeps DESC
FETCH FIRST 20 ROWS ONLY;

3. Identify Blocks Protected by Hot Cache Buffers Chains Latch

-- Find which buffer is protected by the hot cache buffers chains latch child
-- First get the latch address from previous query, then:
SELECT
    bf.file#,
    bf.dbablk          AS block#,
    bf.class#,
    bf.state,
    bf.dirty,
    bf.temp,
    bf.ping,
    bf.lru_flag,
    do.object_name,
    do.object_type,
    do.owner
FROM x$bh             bf
LEFT JOIN dba_objects do ON bf.obj = do.data_object_id
WHERE bf.hladdr = '&hot_latch_address'  -- Address from v$latch_children.addr
ORDER BY bf.tch DESC  -- tch = touch count, higher = hotter
FETCH FIRST 10 ROWS ONLY;

4. Library Cache and Shared Pool Latch Context

-- Hard parse rate — primary driver of shared pool and library cache latch contention
SELECT
    name,
    value,
    ROUND(value / (SELECT (SYSDATE - startup_time) * 3600
                   FROM v$instance), 2) AS per_hour
FROM v$sysstat
WHERE name IN ('parse count (hard)', 'parse count (total)', 'parse count (failures)',
               'execute count', 'cursor authentications')
ORDER BY name;

-- Sessions with high parse counts right now
SELECT
    s.sid,
    s.serial#,
    s.username,
    s.program,
    ss.value   AS hard_parses,
    s.sql_id
FROM v$sesstat   ss
JOIN v$session   s  ON ss.sid     = s.sid
JOIN v$statname  sn ON ss.statistic# = sn.statistic#
WHERE sn.name = 'parse count (hard)'
  AND ss.value > 100
ORDER BY ss.value DESC
FETCH FIRST 20 ROWS ONLY;

5. Shared Pool Free Memory and Fragmentation

-- Shared pool memory breakdown
SELECT
    name,
    bytes / 1024 / 1024 AS mb
FROM v$sgastat
WHERE pool = 'shared pool'
ORDER BY bytes DESC
FETCH FIRST 15 ROWS ONLY;

-- Check for shared pool fragmentation (many small free chunks = fragmentation)
SELECT
    pool,
    name,
    COUNT(*)            AS chunk_count,
    ROUND(SUM(bytes) / 1024 / 1024, 2) AS total_mb,
    ROUND(MIN(bytes) / 1024, 2)         AS min_chunk_kb,
    ROUND(MAX(bytes) / 1024 / 1024, 2)  AS max_chunk_mb,
    ROUND(AVG(bytes) / 1024, 2)         AS avg_chunk_kb
FROM v$shared_pool_reserved
UNION ALL
SELECT
    pool,
    name,
    COUNT(*),
    ROUND(SUM(bytes) / 1024 / 1024, 2),
    ROUND(MIN(bytes) / 1024, 2),
    ROUND(MAX(bytes) / 1024 / 1024, 2),
    ROUND(AVG(bytes) / 1024, 2)
FROM v$sgastat
WHERE pool = 'shared pool'
  AND name = 'free memory'
GROUP BY pool, name;

---

Root Causes

1. Hard Parsing — Shared Pool and Library Cache Latch Contention

Every time Oracle parses a SQL statement for the first time (or cannot reuse an existing cursor), it must acquire the shared pool latch to allocate memory and the library cache latch to insert the new cursor. Hard parsing is the most common driver of shared pool latch contention.

Hard parsing is caused by:

• Non-shared SQL — literals embedded in queries instead of bind variables (WHERE id = 1234 rather than WHERE id = :id)
• Session cursors not cached — applications that close and re-parse the same SQL every execution
• Large number of unique SQL — workloads with huge SQL diversity (ad-hoc query tools, ORMs generating unique SQL per request)
• Shared pool flushes — ALTER SYSTEM FLUSH SHARED_POOL invalidates all cursors, forcing a hard parse storm on next execution

2. Cache Buffers Chains Latch — Hot Blocks

The buffer cache is organized as a hash table. Each hash bucket is a doubly-linked list (chain) of buffer headers, protected by a cache buffers chains latch child. When many sessions concurrently access the same buffer (a hot block), they all serialize on the same latch child protecting that block’s hash bucket.

Root cause is the hot block itself — the same blocks discussed in buffer busy waits. The difference: buffer busy waits is when a session is waiting for another to finish with the buffer; latch: cache buffers chains is when many sessions spin trying to acquire the hash chain latch to even check if the block is in cache.

Both are symptoms of the same underlying hot block problem.

3. Row Cache Objects Latch — Data Dictionary Contention

Oracle caches the data dictionary (table definitions, grants, etc.) in the row cache, protected by the row cache objects latch. Heavy DDL operations, frequent schema changes, or large numbers of sessions doing initial authentication all contend for this latch.

4. Redo Allocation Latch

Under extreme redo generation rates (very high DML throughput), sessions contend for the redo allocation latch to reserve space in the log buffer. Modern Oracle versions use multiple redo copy latches and private redo threads to reduce this, but extreme workloads can still see it.

5. Library Cache Pin Contention

When a DDL statement (e.g., CREATE OR REPLACE PROCEDURE, ALTER TABLE, ANALYZE) runs while other sessions are executing objects that depend on the altered object, those sessions must wait on library cache pin. The DDL needs an exclusive pin to modify the library cache object, but executing sessions hold shared pins.

---

Resolution Steps

Fix Hard Parsing — Use Bind Variables

-- BAD: Literal values cause unique SQL — every value is a separate hard parse
SELECT * FROM orders WHERE order_id = 12345;
SELECT * FROM orders WHERE order_id = 12346;
SELECT * FROM orders WHERE order_id = 12347;
-- Each of these is a unique SQL string, causing separate hard parses

-- GOOD: Bind variables allow cursor reuse
-- In PL/SQL:
EXECUTE IMMEDIATE 'SELECT * FROM orders WHERE order_id = :id'
    USING v_order_id;

-- In application code (Java example pseudocode):
-- PreparedStatement ps = conn.prepareStatement("SELECT * FROM orders WHERE order_id = ?");
-- ps.setInt(1, orderId);

-- Force cursor sharing as a temporary workaround (not recommended long-term):
ALTER SYSTEM SET cursor_sharing = FORCE SCOPE=BOTH;
-- Better: target specific sessions or use SQL patches

Increase Session Cached Cursors

-- Caching cursors at the session level reduces repeated soft parses for the same SQL
-- (Soft parses still require the library cache latch, but less frequently)
ALTER SYSTEM SET session_cached_cursors = 50 SCOPE=BOTH;
-- Increase to 100-200 for connection-pooling environments

-- Check cursor cache effectiveness
SELECT
    name,
    value
FROM v$sysstat
WHERE name IN ('session cursor cache hits', 'parse count (total)');

-- Cache hit ratio should be high (>95% for well-tuned apps)

Resolve Cache Buffers Chains Latch — Address the Hot Block

-- Same resolution as buffer busy waits — eliminate the hot block:
-- 1. Partition the table to distribute access
-- 2. Use ASSM to eliminate segment header hot blocks
-- 3. For index right-edge inserts: reverse key or hash-partition the index
-- 4. Cache small hot reference tables in the KEEP pool

-- As an internal measure: increase the number of hash buckets (reduces
-- collisions in the hash chain, so fewer blocks map to the same latch)
-- This is typically handled automatically; manual intervention via _db_block_hash_buckets
-- is rarely needed and should only be done under Oracle Support guidance

-- Verify hot block remediation is working:
SELECT name, sleeps FROM v$latch WHERE name = 'cache buffers chains';
-- Monitor this over time after making changes

Tune the Shared Pool Size

-- Check shared pool advisory
SELECT
    shared_pool_size_for_estimate / 1024 / 1024   AS size_mb,
    shared_pool_size_factor,
    estd_lc_size,
    estd_lc_memory_objects,
    estd_lc_time_saved,
    estd_lc_load_time,
    estd_lc_load_time_factor
FROM v$shared_pool_advice
ORDER BY shared_pool_size_for_estimate;

-- Increase shared pool if it is undersized (reduces latch contention from ORA-04031 flushes)
ALTER SYSTEM SET shared_pool_size = 2G SCOPE=BOTH;

-- Or via SGA_TARGET (let Oracle manage allocation):
ALTER SYSTEM SET sga_target = 16G SCOPE=BOTH;

-- Protect large objects from aging out:
ALTER SYSTEM SET shared_pool_reserved_size = 100M SCOPE=SPFILE;

Pin Critical PL/SQL Objects to Prevent Invalidation

-- Pin large or frequently-used packages in the shared pool
-- Prevents them from aging out and causing re-parse
EXECUTE DBMS_SHARED_POOL.KEEP('SYS.STANDARD', 'P');
EXECUTE DBMS_SHARED_POOL.KEEP('SYS.DBMS_STANDARD', 'P');
EXECUTE DBMS_SHARED_POOL.KEEP('YOUR_SCHEMA.YOUR_PACKAGE', 'P');

-- Verify pinned objects
SELECT owner, name, type, kept FROM v$db_object_cache
WHERE kept = 'YES'
ORDER BY owner, name;

---

Prevention & Tuning

1. Enforce Bind Variable Usage at the Application Level

This is the single most impactful prevention measure. Review application code and ORM frameworks to ensure parameterized queries are used. Tools like SQL Monitor in Oracle Enterprise Manager can identify the top literal SQL generators.

-- Find SQL with high version counts (symptoms of literal SQL flood)
SELECT
    sql_id,
    version_count,
    SUBSTR(sql_text, 1, 100) AS sql_snippet
FROM v$sqlarea
WHERE version_count > 50
ORDER BY version_count DESC
FETCH FIRST 20 ROWS ONLY;

2. Use Connection Pooling with Cursor Caching

Connection pools that keep sessions alive (rather than creating new connections per request) benefit from SESSION_CACHED_CURSORS. Combine connection pooling with bind variables for maximum cursor reuse.

3. Monitor Latch Hit Ratio Over Time

-- Include latch health in regular AWR reviews
SELECT
    snap_id,
    stat_name,
    value,
    value - LAG(value) OVER (ORDER BY snap_id) AS delta
FROM dba_hist_latch
WHERE stat_name IN ('shared pool', 'cache buffers chains', 'library cache')
  AND snap_id BETWEEN &begin_snap AND &end_snap
ORDER BY snap_id, stat_name;

4. Reduce DDL During Peak Hours

Library cache pin contention spikes during DDL operations on active objects. Schedule package recompilations, ANALYZE statements, and ALTER TABLE during off-peak maintenance windows.

5. Use CURSOR_SHARING Carefully

CURSOR_SHARING = FORCE can mask poor application SQL practices and sometimes causes unexpected plan changes. Use it as a temporary fix only, and work toward proper bind variable usage in application code. CURSOR_SHARING = SIMILAR was deprecated and should not be used.

---

Related Wait Events

• buffer busy waits — Block-level contention; hot blocks cause both latch and buffer busy waits
• db file sequential read — Physical reads that populate the buffer cache, affecting cache buffers chains latch pressure
• enq: TX - row lock contention — Row-level locking; a different serialization mechanism from latches
• cursor: pin S wait on X — Mutex-based cursor contention, the modern successor to library cache latch waits