Oracle V$LATCH Performance Guide - Latch Contention Analysis

Oracle V$LATCH Performance Guide

Understanding and resolving Oracle latch contention for optimal database performance.

What Are Latches?

Latches are low-level serialization mechanisms that protect shared memory structures. Unlike locks, latches are:

• Held for very short periods (microseconds)
• Not queued (requesters spin or sleep)
• Used to protect SGA memory structures

---

Key Latch Views

V$LATCH - System-Wide Statistics

-- Top latches by sleeps (contention)
SELECT
  NAME,
  GETS,
  MISSES,
  SLEEPS,
  SPIN_GETS,
  ROUND(MISSES/NULLIF(GETS, 0) * 100, 2) AS miss_ratio
FROM V$LATCH
WHERE GETS > 0
ORDER BY SLEEPS DESC
FETCH FIRST 10 ROWS ONLY;

V$LATCH_CHILDREN - Child Latches

-- Hot child latches
SELECT
  NAME,
  CHILD#,
  GETS,
  MISSES,
  SLEEPS,
  IMMEDIATE_GETS,
  IMMEDIATE_MISSES
FROM V$LATCH_CHILDREN
WHERE SLEEPS > 0
ORDER BY SLEEPS DESC
FETCH FIRST 20 ROWS ONLY;

V$LATCHHOLDER - Current Holders

-- Who's holding latches now
SELECT
  lh.PID,
  lh.SID,
  lh.LADDR,
  lh.NAME,
  s.USERNAME,
  s.PROGRAM
FROM V$LATCHHOLDER lh
JOIN V$SESSION s ON lh.SID = s.SID;

---

Common Latch Types

Latch Name	Related To	Common Cause
cache buffers chains	Buffer cache	Hot blocks, poor SQL
shared pool	Shared pool	Hard parsing, small pool
library cache	Cursor sharing	Hard parsing, invalidations
redo allocation	Redo generation	High redo volume
redo copy	Redo copying	High redo volume
row cache objects	Dictionary cache	Excessive DDL
simulator lru latch	DB cache	Buffer cache sizing

---

Cache Buffers Chains Latch

Most common latch contention issue.

Diagnose

-- Find hot blocks causing contention
SELECT
  e.OWNER,
  e.OBJECT_NAME,
  e.OBJECT_TYPE,
  ash.CURRENT_FILE#,
  ash.CURRENT_BLOCK#,
  COUNT(*) AS waits
FROM V$ACTIVE_SESSION_HISTORY ash
JOIN DBA_EXTENTS e
  ON ash.CURRENT_FILE# = e.FILE_ID
  AND ash.CURRENT_BLOCK# BETWEEN e.BLOCK_ID AND e.BLOCK_ID + e.BLOCKS - 1
WHERE ash.EVENT LIKE 'latch: cache buffers chains%'
  AND ash.SAMPLE_TIME > SYSDATE - 1/24
GROUP BY e.OWNER, e.OBJECT_NAME, e.OBJECT_TYPE, ash.CURRENT_FILE#, ash.CURRENT_BLOCK#
ORDER BY COUNT(*) DESC
FETCH FIRST 10 ROWS ONLY;

Resolution

-- Option 1: Reduce logical I/O in SQL
-- Tune queries hitting hot blocks

-- Option 2: Partition hot tables
-- Spreads access across multiple blocks

-- Option 3: Use hash clusters for extreme cases
-- Distributes rows by hash value

-- Option 4: Increase _db_block_hash_buckets (hidden parameter)
-- Consult Oracle Support first

---

Shared Pool Latch

Diagnose

-- Check shared pool fragmentation
SELECT
  POOL,
  NAME,
  BYTES/1024/1024 AS mb,
  REQUEST_FAILURES
FROM V$SGASTAT
WHERE POOL = 'shared pool'
ORDER BY BYTES DESC;

-- Check for hard parsing
SELECT
  PARSE_CALLS,
  EXECUTIONS,
  PARSE_CALLS/NULLIF(EXECUTIONS, 0) AS parse_per_exec
FROM V$SQLAREA
WHERE PARSE_CALLS > EXECUTIONS
ORDER BY PARSE_CALLS DESC
FETCH FIRST 10 ROWS ONLY;

Resolution

-- Increase shared pool
ALTER SYSTEM SET SHARED_POOL_SIZE = 2G SCOPE=BOTH;

-- Enable cursor sharing (if appropriate)
ALTER SYSTEM SET CURSOR_SHARING = FORCE;

-- Keep large objects pinned
EXEC DBMS_SHARED_POOL.KEEP('schema.package', 'P');

---

Library Cache Latch

Diagnose

-- Check library cache efficiency
SELECT
  NAMESPACE,
  GETS,
  GETHITRATIO,
  PINS,
  PINHITRATIO,
  RELOADS,
  INVALIDATIONS
FROM V$LIBRARYCACHE;

-- Find SQL with high version count
SELECT SQL_ID, VERSION_COUNT, EXECUTIONS, SQL_TEXT
FROM V$SQLAREA
WHERE VERSION_COUNT > 10
ORDER BY VERSION_COUNT DESC
FETCH FIRST 10 ROWS ONLY;

Resolution

-- Use bind variables
-- Instead of: SELECT * FROM emp WHERE id = 100
-- Use: SELECT * FROM emp WHERE id = :1

-- Reduce cursor invalidations
-- Avoid DDL on tables with active DML

-- Pin frequently used objects
EXEC DBMS_SHARED_POOL.KEEP('SYS.DBMS_OUTPUT', 'P');

---

Redo Latches

Diagnose

-- Redo latch contention
SELECT NAME, GETS, MISSES, SLEEPS
FROM V$LATCH
WHERE NAME IN ('redo allocation', 'redo copy', 'redo writing');

-- Redo generation rate
SELECT * FROM V$SYSSTAT WHERE NAME LIKE 'redo%';

Resolution

-- Increase log buffer (if redo allocation contention)
ALTER SYSTEM SET LOG_BUFFER = 128M SCOPE=SPFILE;

-- Use private redo strands (automatic in 10g+)
-- Check _log_parallelism_dynamic

-- Reduce redo generation
-- Use NOLOGGING for bulk operations where appropriate

---

Mutex vs Latch (10g+)

Oracle replaced some latches with mutexes for better scalability.

-- Check mutex waits
SELECT
  MUTEX_TYPE,
  LOCATION,
  SLEEPS,
  WAIT_TIME
FROM V$MUTEX_SLEEP
ORDER BY SLEEPS DESC
FETCH FIRST 10 ROWS ONLY;

-- Cursor mutex contention
SELECT
  MUTEX_TYPE,
  GETS,
  SLEEPS
FROM V$MUTEX_SLEEP_HISTORY
WHERE MUTEX_TYPE LIKE 'Cursor%'
ORDER BY SLEEPS DESC;

---

Latch Performance Metrics

Miss Ratio

-- Target: < 1% miss ratio
SELECT
  NAME,
  GETS,
  MISSES,
  ROUND(MISSES/NULLIF(GETS, 0) * 100, 4) AS miss_pct
FROM V$LATCH
WHERE GETS > 1000
ORDER BY miss_pct DESC;

Sleep Ratio

-- Sleeps indicate serious contention
SELECT
  NAME,
  MISSES,
  SLEEPS,
  ROUND(SLEEPS/NULLIF(MISSES, 0) * 100, 2) AS sleep_pct
FROM V$LATCH
WHERE MISSES > 0
ORDER BY SLEEPS DESC;

Willing-to-Wait vs Immediate

-- Compare wait modes
SELECT
  NAME,
  GETS AS willing_to_wait,
  IMMEDIATE_GETS AS no_wait,
  MISSES,
  IMMEDIATE_MISSES
FROM V$LATCH
WHERE GETS > 0 OR IMMEDIATE_GETS > 0
ORDER BY GETS DESC
FETCH FIRST 10 ROWS ONLY;

---

Latch Wait Events

-- Current latch waits
SELECT
  SID,
  EVENT,
  P1TEXT,
  P1,
  P2TEXT,
  P2,
  SECONDS_IN_WAIT
FROM V$SESSION
WHERE EVENT LIKE 'latch%'
AND STATE = 'WAITING';

-- Historical latch waits
SELECT
  EVENT,
  COUNT(*) AS samples,
  ROUND(COUNT(*) * 100 / SUM(COUNT(*)) OVER(), 2) AS pct
FROM V$ACTIVE_SESSION_HISTORY
WHERE EVENT LIKE 'latch%'
  AND SAMPLE_TIME > SYSDATE - 1
GROUP BY EVENT
ORDER BY COUNT(*) DESC;

---

Key Metrics Summary

Metric	Good	Warning	Critical
Miss Ratio	< 0.5%	0.5-1%	> 1%
Sleep Ratio	< 1%	1-5%	> 5%
Spin Gets/Misses	> 90%	80-90%	< 80%

---

Related Resources

• Wait Events Cheat Sheet
• V$ Views Reference
• Performance Analysis Scripts
• AWR Report Guide