Latch Wait Summary (latchsum.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 a summary of Oracle latch contention by analyzing which latches processes are currently waiting for. It groups processes by the specific latch they are waiting on and shows the total number of sessions waiting for each latch type, helping identify latch bottlenecks.

Script

rem  latchsum.sql
rem
ttitle 'Latch Wait Summary'
rem
col latchwait format a8 heading 'LATCH|WAIT'
col name format a32 heading 'LATCH NAME'
col session_count format 9999 heading 'SESSION|COUNT'
rem
break on report
compute sum of session_count on report
rem
select latchwait,
       name,
       count(*) session_count
  from v$process p, v$latch l
 where p.latchwait = l.addr(+)
 group by p.latchwait, name;

Usage

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

-- No parameters required
-- Shows current latch waits at time of execution

Required Privileges

• SELECT on V$PROCESS
• SELECT on V$LATCH
• Typically requires DBA role

Sample Output

Latch Wait Summary

LATCH    LATCH NAME                       SESSION
WAIT                                      COUNT
-------- -------------------------------- -------
         cache buffers chains                  5
         redo allocation                       3
         shared pool                           2
         library cache                         1
                                         -------
sum                                           11

Key Output Columns

• LATCH WAIT: Latch address (null if no wait)
• LATCH NAME: Name of the latch being waited for
• SESSION COUNT: Number of processes waiting for this latch

Understanding Latch Contention

Common Latch Types and Meanings

• cache buffers chains: Buffer cache block access contention
• redo allocation: Redo log buffer allocation conflicts
• shared pool: Shared pool memory allocation issues
• library cache: SQL parsing and library cache access
• redo copy: Redo log buffer copy mechanism waits

Interpreting Results

• High session counts: Indicate significant contention points
• Multiple latch types: System-wide performance issues
• Single dominant latch: Specific bottleneck to address
• No waits: System performing well at time of check

Performance Analysis

Critical Latch Contention Scenarios

1. cache buffers chains (>10 sessions):

   • Hot blocks causing buffer cache contention
   • Consider increasing buffer cache or finding hot blocks

2. redo allocation (>5 sessions):

   • Redo log buffer too small or log writer bottleneck
   • Increase log buffer size or optimize log writer I/O

3. shared pool (>3 sessions):

   • Shared pool memory pressure
   • Increase shared pool size or reduce SQL parsing

4. library cache (>2 sessions):

   • Hard parsing or library cache locks
   • Improve SQL reuse or resolve library cache issues

Common Use Cases

Performance Troubleshooting

-- During performance issues
@latchsum.sql
-- Look for high session counts on specific latches

Proactive Monitoring

-- Include in health check scripts
@latchsum.sql
-- Baseline normal vs problem periods

Capacity Planning

-- Monitor latch contention trends
@latchsum.sql
-- Document growth in latch waits over time

Troubleshooting High Latch Waits

For cache buffers chains

1. Identify hot blocks using hotblocks.sql
2. Increase DB_CACHE_SIZE if memory available
3. Tune SQL to reduce logical reads
4. Consider partitioning for hot tables

For redo allocation

1. Increase LOG_BUFFER parameter
2. Optimize redo log placement (faster disks)
3. Reduce commit frequency in applications
4. Check log writer I/O performance

For shared pool

1. Increase SHARED_POOL_SIZE
2. Enable cursor sharing (CURSOR_SHARING=FORCE)
3. Pin frequently used packages in shared pool
4. Reduce hard parsing through bind variables

Related Scripts

• Detailed Latch Statistics - Comprehensive latch analysis
• Hot Blocks Analysis - Find cache buffers chains contention
• Buffer Cache Analysis - Buffer pool performance
• Active Session Analysis - Current session activity