Latch Names Lookup (vlatchname.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.

Latch Names Lookup

This script provides a simple lookup utility to find Oracle latch names and their corresponding latch numbers, essential for performance analysis and troubleshooting latch contention issues.

Script: vlatchname.sql

select latch#, name
from v$latchname
where latch# like '&latchnum'
order by 1
/

What This Script Does

This script provides latch information lookup by:

• Interactive Search: Prompts for latch number pattern using LIKE operator
• Number-to-Name Mapping: Shows latch numbers with their descriptive names
• Pattern Matching: Supports wildcards for finding related latches
• Ordered Results: Lists latches in numerical order
• Simple Interface: Quick lookup tool for performance analysis

Usage

-- Find a specific latch by number
@vlatchname.sql
Enter value for latchnum: 177

-- Find all latches (use wildcard)
@vlatchname.sql
Enter value for latchnum: %

-- Find latches in a range
@vlatchname.sql
Enter value for latchnum: 17%

-- Direct usage without prompting
SELECT latch#, name FROM v$latchname WHERE name LIKE '%cache%';

Required Privileges

• SELECT privilege on V$LATCHNAME
• Typically requires DBA role or SELECT_CATALOG_ROLE

Sample Output

    LATCH# NAME
---------- --------------------------------------------------
       177 cache buffers chains
       178 cache buffers lru chain
       179 checkpoint queue latch
       180 redo writing
       181 redo allocation
       182 archive process latch
       183 library cache
       184 library cache pin
       185 library cache pin allocation
       186 shared pool

Understanding Oracle Latches

What Are Latches?

• Serialization Mechanisms: Protect shared memory structures in Oracle
• Low-Level Locks: Lightweight, short-duration locks
• Performance Critical: Essential for Oracle’s internal operations
• Contention Points: Can become bottlenecks under heavy load

Common Latch Types

• Cache Buffers Chains: Protects buffer cache hash chains
• Library Cache: Protects shared SQL and PL/SQL areas
• Shared Pool: Protects shared pool memory allocation
• Redo Allocation: Protects redo log buffer allocation
• LRU Chain: Protects buffer cache LRU chain operations

Common Use Cases

1. Latch Contention Investigation

-- After identifying latch contention, find the latch name
@vlatchname.sql
Enter value for latchnum: 177

-- Find all cache-related latches
SELECT latch#, name FROM v$latchname WHERE name LIKE '%cache%';

-- Find all redo-related latches
SELECT latch#, name FROM v$latchname WHERE name LIKE '%redo%';

2. Performance Analysis Correlation

-- Correlate with latch statistics
SELECT ln.name, ls.gets, ls.misses, ls.sleeps
FROM v$latch ls, v$latchname ln
WHERE ls.latch# = ln.latch#
  AND ln.latch# = 177
ORDER BY ls.sleeps DESC;

-- Find top latch contention
SELECT ln.name, ls.sleeps, ls.wait_time
FROM v$latch ls, v$latchname ln
WHERE ls.latch# = ln.latch#
  AND ls.sleeps > 0
ORDER BY ls.sleeps DESC;

3. Historical Analysis

-- AWR-based latch analysis
SELECT ln.name,
       SUM(sleeps_delta) as total_sleeps,
       SUM(wait_time_delta) as total_wait_time
FROM dba_hist_latch h, v$latchname ln
WHERE h.latch_number = ln.latch#
  AND h.snap_id BETWEEN &begin_snap AND &end_snap
GROUP BY ln.name
ORDER BY total_sleeps DESC;

4. Specific Latch Categories

-- Library cache latches
SELECT latch#, name FROM v$latchname
WHERE name LIKE '%library%' OR name LIKE '%cache%'
ORDER BY latch#;

-- Memory management latches
SELECT latch#, name FROM v$latchname
WHERE name LIKE '%shared pool%' OR name LIKE '%heap%'
ORDER BY latch#;

-- I/O related latches
SELECT latch#, name FROM v$latchname
WHERE name LIKE '%buffer%' OR name LIKE '%file%'
ORDER BY latch#;

Integration with Performance Analysis

1. Latch Wait Analysis

-- Current latch waits
SELECT s.sid, s.username, s.event, s.p1, s.p2, ln.name
FROM v$session s, v$latchname ln
WHERE s.event LIKE 'latch%'
  AND s.p2 = ln.latch#;

-- Latch holder identification
SELECT s.sid, s.username, s.program, ln.name
FROM v$session s, v$latchholder lh, v$latchname ln
WHERE s.sid = lh.sid
  AND lh.latch# = ln.latch#;

2. Comprehensive Latch Report

-- Complete latch analysis
SELECT
  ln.name,
  l.gets,
  l.misses,
  l.sleeps,
  l.immediate_gets,
  l.immediate_misses,
  ROUND(l.misses/l.gets*100, 2) as miss_ratio,
  ROUND(l.sleeps/l.misses*100, 2) as sleep_ratio
FROM v$latch l, v$latchname ln
WHERE l.latch# = ln.latch#
  AND l.gets > 0
ORDER BY l.sleeps DESC;

3. Latch Trends Over Time

-- Hourly latch activity
SELECT
  TO_CHAR(sample_time, 'YYYY-MM-DD HH24') as hour,
  event,
  COUNT(*) as wait_count
FROM v$active_session_history
WHERE event LIKE 'latch%'
  AND sample_time >= SYSDATE - 1
GROUP BY TO_CHAR(sample_time, 'YYYY-MM-DD HH24'), event
ORDER BY hour, wait_count DESC;

Troubleshooting Common Latch Issues

1. Cache Buffers Chains Latch

-- Find hot blocks causing contention
SELECT obj.owner, obj.object_name, obj.object_type
FROM v$bh bh, dba_objects obj
WHERE bh.obj = obj.object_id
  AND bh.file# || '-' || bh.block# IN (
    SELECT p1 || '-' || p2
    FROM v$session_wait
    WHERE event = 'latch: cache buffers chains'
  );

2. Library Cache Latch

-- Find frequently parsed SQL
SELECT sql_text, parse_calls, executions
FROM v$sqlarea
WHERE parse_calls > 100
ORDER BY parse_calls DESC;

3. Shared Pool Latch

-- Check shared pool fragmentation
SELECT pool, name, bytes/(1024*1024) as mb
FROM v$sgastat
WHERE pool = 'shared pool'
  AND name = 'free memory'
ORDER BY bytes DESC;

Oracle Version Considerations

Version Differences

• Oracle 9i and earlier: Fewer latch types
• Oracle 10g+: Additional latches for new features
• Oracle 11g+: Mutex introduction reduces some latch usage
• Oracle 12c+: Additional latches for new functionality

Evolution of Latches

-- Count latches by version (documentation purposes)
SELECT COUNT(*) as total_latches FROM v$latchname;

-- Find version-specific latches
SELECT name FROM v$latchname
WHERE name LIKE '%mutex%' OR name LIKE '%adaptive%';

Best Practices

Analysis Guidelines

1. Start with Wait Events: Use latch waits in V$SESSION_WAIT first
2. Identify Patterns: Look for consistent latch contention
3. Correlate with Application: Match latch waits to application activity
4. Historical Analysis: Use AWR/StatsPack for trend analysis
5. Focus on Sleeps: Latch sleeps indicate contention

Performance Tuning

1. Application Changes: Often most effective for latch reduction
2. Parameter Tuning: Adjust relevant Oracle parameters
3. SQL Optimization: Reduce parsing for library cache latches
4. Buffer Cache: Tune for cache buffers chains issues
5. Shared Pool: Size appropriately to reduce allocation latches

Integration with Other Scripts

This script works well with:

• Latch Statistics (vlatch.sql) - For detailed latch statistics
• Active Session Analysis (gvsess.sql) - For session-level latch waits
• Wait Event Analysis (vsysev.sql) - For system-wide wait analysis
• Library Cache Analysis (vlibcache.sql) - For library cache latch issues

Quick Reference

Most Common Latches

• 177: cache buffers chains
• 183: library cache
• 186: shared pool
• 181: redo allocation
• 198: enqueue hash chains

This simple but essential script provides the foundation for latch-based performance analysis in Oracle databases.