Detailed Latch Statistics (vlatchs.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 script provides comprehensive latch performance analysis by examining detailed latch statistics including hit ratios, sleep patterns, and contention metrics. It helps DBAs identify latch contention issues that can significantly impact database performance and diagnose serialization bottlenecks in Oracle systems.

Script

rem  vlatchs.sql
rem
ttitle 'Detailed Latch Statistics ( /1000 )'
rem
set linesize 132
rem
col latch# format 999 heading 'ID'
col name format a24 trunc heading 'NAME'
col gets format b9999999 heading 'GETS'
col misses format b9999999 heading 'MISSES'
col sleeps format b999999 heading 'SLEEPS'
col immediate_gets format b999999999 heading 'IMMED|GETS'
col immediate_misses format b99999999 heading 'IMMED|MISSES'
col immed_pct format b99.9 heading 'IMMED|PCT'
col spin_gets format b999999 heading 'SPIN|GETS'
col sleep1 format b99999 heading '1'
col sleep2 format b9999 heading '2'
col sleep3 format b9999 heading '3'
col sleep4 format b999 heading '4'
col sleep5 format b999 heading '5'
col sleep6 format b999 heading '6'
col sleep7 format b99 heading '7'
col sleep8 format b99 heading '8'
col sleep9 format b99 heading '9'
col sleep10 format b99 heading '10'
col sleep11 format b99 heading '11'
col miss_pct format 99.9 heading 'MISS|PCT'
col sleeps_pct format 999.9 heading 'SLEEP|PCT'
col spin_gets_pct format 999.9 heading 'SPIN|GET|PCT'
rem
break on report
compute sum of gets misses sleeps immediate_gets immediate_misses -
               spin_gets sleep1 sleep2 sleep3 sleep4 sleep5 sleep6 -
               sleep7 sleep8 sleep9 sleep10 sleep11 -
     on report
rem
select latch#,
       name,
       gets/1000 gets,
       misses/1000 misses,
       misses * 100 / decode( gets, 0, null, gets ) miss_pct,
       spin_gets/1000 spin_gets,
       spin_gets * 100 / decode( misses, 0, null, misses ) spin_gets_pct,
       sleeps/1000 sleeps,
       sleeps * 100 / decode( misses, 0, null, misses ) sleeps_pct,
       sleep1/1000 sleep1,
       sleep2/1000 sleep2,
       sleep3/1000 sleep3,
/*
       sleep4/1000 sleep4,
       sleep5/1000 sleep5,
       sleep6/1000 sleep6,
       sleep7/1000 sleep7,
       sleep8/1000 sleep8,
       sleep9/1000 sleep9,
       sleep10/1000 sleep10,
       sleep11/1000 sleep11,
*/
       immediate_gets/1000 immediate_gets,
       immediate_misses/1000 immediate_misses,
       immediate_misses * 100 /
           decode( immediate_gets, 0, null, immediate_gets ) immed_pct
  from v$latch
 order by name;

Usage

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

-- No parameters required
-- Shows current latch statistics since instance startup
-- Values displayed in thousands for readability

Required Privileges

• SELECT on V$LATCH
• Available to most database users

Sample Output

Detailed Latch Statistics ( /1000 )

 ID NAME                       GETS   MISSES MISS SPIN  SPIN SLEEPS SLEEP    1    2    3 IMMED    IMMED IMMED
                                       PCT    GETS  GET              PCT                  GETS   MISSES   PCT
--- ------------------------ ------- ------- ---- ----- ---- ------ ----- ---- ---- ---- ------- ------- -----
  1 buffer hash table chain   1,456     127  8.7    89 70.1     38  29.9   25   10    3     234       5   2.1
 15 cache buffer chain       12,345     567  4.6   234 41.3    333  58.7  156   89   45   5,678      78   1.4
 45 library cache               890      45  5.1    23 51.1     22  48.9   12    7    3     456       8   1.8
 67 row cache objects           678      23  3.4    15 65.2      8  34.8    5    2    1     234       3   1.3
 78 session allocation          456      12  2.6     8 66.7      4  33.3    3    1    0     123       2   1.6
123 shared pool                 789      34  4.3    18 52.9     16  47.1   10    4    2     345       6   1.7
--- ------------------------ ------- ------- ---- ----- ---- ------ ----- ---- ---- ---- ------- ------- -----
sum                          16,614     808          385         421        211  113   54   7,070     102

Key Output Columns

• ID: Latch number identifier
• NAME: Descriptive latch name
• GETS: Total latch acquisition requests (/1000)
• MISSES: Failed immediate acquisitions (/1000)
• MISS PCT: Percentage of gets that resulted in misses
• SPIN GETS: Successful acquisitions after spinning (/1000)
• SPIN GET PCT: Percentage of misses resolved by spinning
• SLEEPS: Acquisitions requiring sleep (/1000)
• SLEEP PCT: Percentage of misses requiring sleep
• 1,2,3: Sleep counts for first three sleep rounds (/1000)
• IMMED GETS: Immediate mode acquisitions (/1000)
• IMMED MISSES: Failed immediate acquisitions (/1000)
• IMMED PCT: Percentage of immediate gets that missed

Understanding Latch Statistics

Latch Acquisition Process

1. Immediate Get: Try to acquire latch without waiting
2. Spin: If busy, spin for short time checking availability
3. Sleep: If still busy, sleep and retry with exponential backoff

Performance Indicators

• Low Miss PCT (< 1%): Good latch efficiency
• High Spin Get PCT: Successful spinning behavior
• Low Sleep PCT: Minimal blocking behavior
• Low Immediate Miss PCT: Good immediate acquisition rate

Critical Latches to Monitor

• cache buffer chains: Buffer cache contention
• library cache: SQL parsing and cursor contention
• shared pool: Memory allocation contention
• row cache objects: Data dictionary cache contention
• redo allocation: Redo log buffer contention

Performance Analysis

Identifying Contention

1. High miss percentages (>2%): Indicates contention
2. High sleep percentages (>10%): Severe contention
3. Many sleep rounds: Extended wait times
4. High immediate miss percentages: Memory pressure

Common Contention Patterns

-- Focus on high-contention latches
SELECT name, gets, misses,
       ROUND(misses * 100 / NULLIF(gets, 0), 2) miss_pct,
       sleeps,
       ROUND(sleeps * 100 / NULLIF(misses, 0), 2) sleep_pct
FROM v$latch
WHERE gets > 1000
AND misses * 100 / NULLIF(gets, 0) > 1
ORDER BY miss_pct DESC;

Common Use Cases

Performance Troubleshooting

-- During performance issues
@vlatchs.sql
-- Look for high miss percentages
-- Focus on cache buffer chains and library cache

System Health Check

-- Regular monitoring
@vlatchs.sql
-- Establish baseline miss rates
-- Track trends over time

Capacity Planning

-- Before system changes
@vlatchs.sql
-- Document current latch behavior
-- Plan for increased workload

Troubleshooting High Latch Contention

Cache Buffer Chains Contention

1. Identify hot blocks:

   SELECT obj, file#, dbarfil, dbablk, COUNT(*)
   FROM v$bh
   WHERE state != 0
   GROUP BY obj, file#, dbarfil, dbablk
   HAVING COUNT(*) > 1
   ORDER BY COUNT(*) DESC;

2. Consider solutions:

   • Increase buffer cache size
   • Partition hot tables
   • Use result cache for frequently accessed data

Library Cache Contention

1. Check shared pool sizing:

   SELECT pool, name, bytes/1024/1024 mb
   FROM v$sgastat
   WHERE pool = 'shared pool'
   ORDER BY bytes DESC;

2. Optimization strategies:

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

Row Cache Objects Contention

1. Monitor data dictionary cache:

   SELECT parameter, gets, getmisses,
          ROUND(getmisses*100/gets, 2) miss_pct
   FROM v$rowcache
   WHERE gets > 0
   ORDER BY miss_pct DESC;

2. Tuning approaches:

   • Increase shared pool size
   • Review data dictionary access patterns

Advanced Analysis

Historical Latch Trends

-- Compare current with historical data
SELECT h.snap_id, h.end_interval_time,
       l.latch_name,
       l.gets, l.misses,
       ROUND(l.misses * 100 / NULLIF(l.gets, 0), 2) miss_pct
FROM dba_hist_latch l, dba_hist_snapshot h
WHERE l.snap_id = h.snap_id
AND l.latch_name IN ('cache buffer chains', 'library cache', 'shared pool')
AND h.end_interval_time > SYSDATE - 7
ORDER BY h.end_interval_time DESC, l.latch_name;

Latch Sleep Analysis

-- Analyze sleep patterns for problematic latches
SELECT name,
       sleeps,
       sleep1, sleep2, sleep3,
       CASE
         WHEN sleep1 > sleep2 * 2 THEN 'Quick Resolution'
         WHEN sleep3 > sleep1 THEN 'Extended Waits'
         ELSE 'Normal Pattern'
       END sleep_pattern
FROM v$latch
WHERE sleeps > 100
ORDER BY sleeps DESC;

Workload Correlation

-- Correlate latch contention with system activity
SELECT
  (SELECT SUM(gets) FROM v$latch WHERE name LIKE '%cache buffer%') cache_gets,
  (SELECT value FROM v$sysstat WHERE name = 'session logical reads') logical_reads,
  (SELECT value FROM v$sysstat WHERE name = 'physical reads') physical_reads
FROM dual;

Related Scripts

• Basic Latch Information (vlatch.sql) - Simplified latch view
• Latch Summary (latchsum.sql) - Aggregated latch statistics
• Buffer Pool Statistics (gvbp.sql) - Buffer cache analysis
• Library Cache Statistics (vlibcache.sql) - Library cache details