Comprehensive Latch Statistics (vlatch.sql)

Use at Your Own Risk

This script is for educational purposes only. Always test in development environments before production use. See our disclaimer for full terms.

What This Script Does

This comprehensive script analyzes Oracle latch performance by providing detailed statistics on latch gets, misses, sleeps, and spin operations. It calculates efficiency percentages and shows which processes (if any) are currently holding latches, making it essential for diagnosing latch contention issues.

Script

rem  vlatch.sql
rem
ttitle 'Latch Statistics'
rem
set lines 140
rem
col name format a27 heading 'NAME'
col pid format 90 heading 'PID'
col gets format b999,999,999 heading 'GETS'
col misses format b9,999,999 heading 'MISSES'
col miss_pct format b999.9 heading 'MISS|PCT'
col sleeps format b9,999,999 heading 'SLEEPS'
col sleep_pct format b999.9 heading 'SLEEP|PCT'
col spin_gets format b9,999,999 heading 'SPIN|GETS'
col spin_gets_pct format b999.9 heading 'SPIN|GETS|PCT'
col immediate_gets format b999,999,999 heading 'IMMEDIATE|GETS'
col immediate_misses format b99,999,999 heading 'IMMEDIATE|MISSES'
col imiss_pct format b999.9 heading 'IMMEDIATE|MISS|PCT'
rem
break on report
compute sum of gets misses sleeps immediate_gets immediate_misses on report
rem
select n.name, h.pid,
       l.gets,
       l.misses,
       (l.misses / decode(l.gets,0,1, l.gets)) * 100 miss_pct,
       l.sleeps,
       (l.sleeps / decode(l.misses,0,1, l.misses)) * 100 sleep_pct,
       l.spin_gets,
       (l.spin_gets / decode(l.misses,0,1, l.misses)) * 100 spin_gets_pct,
       l.immediate_gets,
       l.immediate_misses,
       (l.immediate_misses /
          decode((l.immediate_gets + l.immediate_misses),
                   0, 1,
                   (l.immediate_gets + l.immediate_misses))) * 100 imiss_pct
  from v$latchholder h, v$latchname n, v$latch l
 where l.latch# = n.latch#
   and l.addr = h.laddr(+)
 order by n.name;
rem
set lines 80

Usage

-- Run the script in SQL*Plus or SQLcl
@vlatch.sql

-- No parameters required
-- Shows cumulative statistics since instance startup

Required Privileges

• SELECT on V$LATCHHOLDER
• SELECT on V$LATCHNAME
• SELECT on V$LATCH
• Generally available to most database users

Sample Output

Latch Statistics

NAME                        PID      GETS   MISSES MISS   SLEEPS SLEEP SPIN    SPIN  IMMEDIATE IMMEDIATE IMMEDIATE
                                             PCT           PCT   GETS   GETS  GETS      MISSES    MISS
                                                                        PCT                      PCT
--------------------------- --- --------- -------- ----- ------ ----- ------ ----- --------- --------- ---------
cache buffers chains            145678901    12345   0.0      0   0.0      0   0.0 234567890         0       0.0
library cache                    98765432     5678   0.6    123  21.6    234  41.2  45678901        12       0.0
redo allocation                  45678901      234   0.5     45  19.2     89  38.0  12345678         2       0.0
shared pool                      23456789      345   1.5     67  19.4    123  35.7   8901234         5       0.1

Key Output Columns

Basic Latch Operations

• NAME: Latch name
• PID: Process ID currently holding the latch (if any)
• GETS: Total number of latch get requests
• MISSES: Number of times latch was not immediately available
• MISS PCT: Percentage of gets that resulted in misses

Contention Indicators

• SLEEPS: Number of times process had to sleep waiting for latch
• SLEEP PCT: Percentage of misses that resulted in sleeps
• SPIN GETS: Number of successful gets after spinning
• SPIN GETS PCT: Percentage of misses resolved by spinning

Immediate Mode Operations

• IMMEDIATE GETS: Latch gets in immediate mode (no waiting)
• IMMEDIATE MISSES: Immediate mode requests that failed
• IMMEDIATE MISS PCT: Percentage of immediate mode failures

Understanding Latch Performance

Critical Latch Types

• cache buffers chains: Buffer cache block access
• library cache: SQL parsing and object access
• redo allocation: Redo log buffer allocation
• shared pool: Shared pool memory allocation
• row cache objects: Data dictionary cache access

Performance Thresholds

• Miss PCT < 1%: Generally acceptable
• Miss PCT 1-3%: Monitor closely, may need tuning
• Miss PCT > 3%: Significant contention requiring action
• Sleep PCT > 50%: High contention, immediate attention needed

Spin vs Sleep Analysis

• High SPIN GETS PCT: Latches released quickly (good)
• High SLEEP PCT: Long hold times causing sleeping (concern)
• Low SPIN GETS PCT: May indicate CPU constraints

Performance Analysis

Healthy Latch Indicators

1. Low miss percentages (< 1%)
2. High spin success rates (> 80% of misses resolved by spinning)
3. Low sleep percentages (< 20% of misses result in sleeps)
4. No persistent latch holders (PID column mostly empty)

Contention Warning Signs

1. High miss percentages (> 3%)
2. High sleep counts with low spin success
3. Persistent latch holders (same PID appearing repeatedly)
4. Growing statistics during monitoring periods

Common Use Cases

Performance Troubleshooting

-- During system slowdowns
@vlatch.sql
-- Focus on latches with high MISS PCT or SLEEP PCT
-- Identify specific contention points

Proactive Monitoring

-- Regular health checks
@vlatch.sql
-- Establish baselines for comparison
-- Monitor trends over time

Capacity Planning

-- Analyze latch usage patterns
@vlatch.sql
-- Plan for increased concurrency
-- Size shared memory areas appropriately

Troubleshooting Latch Contention

Cache Buffers Chains Contention

1. High miss/sleep percentages:

   -- Find hot blocks
   SELECT obj, COUNT(*)
   FROM v$bh
   WHERE tch > 1000
   GROUP BY obj
   ORDER BY COUNT(*) DESC;

2. Solutions:

   • Increase buffer cache size
   • Optimize SQL to reduce logical reads
   • Consider partitioning for hot tables

Library Cache Contention

1. Symptoms: High miss percentages on library cache latch

2. Investigation:

   -- Check for hard parsing
   SELECT sql_text, parse_calls, executions
   FROM v$sqlarea
   WHERE parse_calls = executions
   AND executions > 100;

3. Solutions:

   • Increase shared pool size
   • Use bind variables to reduce parsing
   • Pin frequently used packages

Redo Allocation Contention

1. Symptoms: High contention on redo allocation latch
2. Solutions:

   -- Increase log buffer
   ALTER SYSTEM SET log_buffer = 32M;
   
   -- Add redo log groups/members
   -- Optimize commit frequency

Shared Pool Contention

1. Investigation:

   -- Check shared pool fragmentation
   SELECT pool, name, bytes
   FROM v$sgastat
   WHERE pool = 'shared pool'
   ORDER BY bytes DESC;

2. Solutions:

   • Increase shared pool size
   • Use shared pool reserved area
   • Reduce cursor sharing issues

Advanced Analysis

Latch Efficiency Calculation

-- Calculate overall latch efficiency
SELECT
  SUM(gets) total_gets,
  SUM(misses) total_misses,
  ROUND((1 - SUM(misses)/SUM(gets)) * 100, 2) efficiency_pct
FROM v$latch;

Latch Wait Time Analysis

-- Combine with wait events
SELECT event, total_waits, time_waited,
       ROUND(time_waited/total_waits, 2) avg_wait_time
FROM v$system_event
WHERE event LIKE '%latch%'
ORDER BY time_waited DESC;

Historical Trend Analysis

-- Track latch statistics over time using AWR
SELECT snap_id, latch_name, gets, misses,
       ROUND(misses/gets*100, 2) miss_pct
FROM dba_hist_latch
WHERE latch_name IN ('cache buffers chains', 'library cache')
ORDER BY snap_id DESC, latch_name;

Current Latch Holders

-- Find processes currently holding latches
SELECT p.spid, s.username, s.program, l.name latch_name
FROM v$process p, v$session s, v$latchholder h, v$latchname l
WHERE p.addr = s.paddr
AND s.sid = h.sid
AND h.laddr = l.addr;

Related Scripts

• Latch Wait Summary (latchsum.sql) - Simplified latch wait analysis
• Active Session Analysis (gvsess.md) - Session-level latch waits
• Buffer Cache Analysis (gvbp.md) - Related buffer cache performance
• Shared Pool Analysis (show_sga.md) - Memory pool efficiency