StatsPack Latch Analysis (platch.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 Oracle script provides comprehensive historical latch performance analysis using StatsPack repository data. It analyzes latch contention patterns over time by calculating delta values between consecutive snapshots, showing gets, misses, spins, and sleeps with rates and percentages. This is essential for identifying performance trends, understanding latch contention patterns, and supporting historical performance analysis.

The Script

rem platch.sql
rem
set lines 132
rem
col SNAP_ID format 999999 heading 'SNAP ID'
col snap_time format a15 heading 'SNAP TIME'
col name format a12 heading 'NAME'
col gets format 999999999999 heading 'GETS'
col gets_per_sec format 9999999 heading 'GETS|PER|SEC'
col misses format 999999999999 heading 'MISSES'
col misses_per_sec format 99999 heading 'MISSES|PER|SEC'
col spin_gets format 999999999999 heading 'SPIN GETS'
col miss_pct format 990.0 heading 'MISS PCT'
col spin_get_pct format 990.0 heading 'SPIN|GET|PCT'
col sleep_pct format 990.0 heading 'SLEEP|PCT'
col sleeps_per_sec format 99999 heading 'SLEEPS|PER|SEC'

define start_dt='&start_date'
define end_dt='&end_date'
define latnam='&latch_name'

ttitle 'StatsPack Latch Statistics|''&latnam'''

select /*+ ordered use_nl(e x b se sb) */
       e.snap_id,
       to_char(e.snap_time, 'MM/DD HH24:MI:SS') snap_time,
       sum(se.gets - sb.gets) gets,
       sum(se.gets - sb.gets) / ((e.snap_time - b.snap_time) * 24 * 60 * 60) gets_per_sec,
       sum(se.misses - sb.misses) misses,
       sum(se.misses - sb.misses) / ((e.snap_time - b.snap_time) * 24 * 60 * 60) misses_per_sec,
       sum(se.misses - sb.misses) / decode(sum(se.gets - sb.gets),0,1,sum(se.gets - sb.gets)) *100 miss_pct,
       sum(se.spin_gets - sb.spin_gets) spin_gets,
       sum(se.spin_gets - sb.spin_gets) / decode(sum(se.misses - sb.misses),0,1,sum(se.misses - sb.misses)) *100 spin_get_pct,
       sum(se.sleeps - sb.sleeps) sleeps,
       sum(se.sleeps - sb.sleeps) / ((e.snap_time - b.snap_time) * 24 * 60 * 60) sleeps_per_sec,
       sum(se.sleeps - sb.sleeps) / decode(sum(se.misses - sb.misses),0,1,sum(se.misses - sb.misses)) *100 sleep_pct
from stats$snapshot e,
     stats$snapshot b,
     stats$latch se,
     stats$latch sb
where b.snap_id =
         (select max(x.snap_id)
      from stats$snapshot x
     where x.snap_id < e.snap_id
       and x.dbid = e.dbid)
  and b.dbid = e.dbid
  and b.instance_number = e.instance_number
  and se.snap_id = e.snap_id
  and se.dbid = e.dbid
  and se.instance_number = e.instance_number
  and sb.snap_id = b.snap_id
  and sb.dbid = b.dbid
  and sb.instance_number = b.instance_number
  and e.snap_time >= nvl('&start_dt', trunc(sysdate))
  and e.snap_time <= nvl(to_date('&end_dt'), sysdate)+1
  and se.name like lower('&latnam')
  and sb.latch# = se.latch#
group by
       e.snap_id,
       to_char(e.snap_time, 'MM/DD HH24:MI:SS'),
       ((e.snap_time - b.snap_time) * 24 * 60 * 60)
order by
      e.snap_id
/

Usage

-- Run the script and provide parameters when prompted
@platch.sql
-- Enter value for start_date: 01-JAN-2024
-- Enter value for end_date: 02-JAN-2024
-- Enter value for latch_name: %library cache%

-- Analyze specific latch over last 7 days
@platch.sql
-- Enter value for start_date: (press Enter for today)
-- Enter value for end_date: (press Enter for now)
-- Enter value for latch_name: shared pool

-- Check all latches with high contention
@platch.sql
-- Enter value for start_date:
-- Enter value for end_date:
-- Enter value for latch_name: %

Required Privileges

• SELECT privilege on StatsPack tables:
  • STATS$SNAPSHOT
  • STATS$LATCH

Sample Output

                           StatsPack Latch Statistics
                                'library cache'

SNAP ID SNAP TIME         GETS GETS   MISSES MISSES MISS   SPIN GETS SPIN    SLEEPS SLEEPS SLEEP
                               PER           PER    PCT             GET             PER    PCT
                               SEC           SEC                    PCT             SEC
------- --------------- ------ ------- ------ ------ ---- --------- ----- --------- ------ -----
   1234 12/15 09:00:00 456789    1234     89     12  1.2    45678   51.2        23      3   25.8
   1235 12/15 10:00:00 523456    1456    145     23  2.8    67890   46.8        67      8   46.2
   1236 12/15 11:00:00 398765    1108     67     11  1.7    34567   51.6        34      4   50.7

Key Output Columns

• SNAP ID: StatsPack snapshot identifier
• SNAP TIME: Snapshot time (MM/DD HH24:MI:SS format)
• GETS: Total latch gets during the interval
• GETS PER SEC: Latch gets per second
• MISSES: Latch get attempts that failed initially
• MISSES PER SEC: Latch misses per second
• MISS PCT: Percentage of gets that resulted in misses
• SPIN GETS: Successful gets after spinning
• SPIN GET PCT: Percentage of misses resolved by spinning
• SLEEPS: Times processes had to sleep waiting for latch
• SLEEPS PER SEC: Sleep events per second
• SLEEP PCT: Percentage of misses that resulted in sleeps

Understanding Latch Statistics

Latch Operation Flow

1. Initial Get Attempt

   • Process attempts to acquire latch
   • If available: immediate success (counted in GETS)
   • If unavailable: results in MISS

2. Spin Phase

   • Process spins (busy wait) for short period
   • If latch becomes available: SPIN_GET success
   • If still unavailable: proceed to sleep

3. Sleep Phase

   • Process goes to sleep on wait list
   • Counted as SLEEP event
   • Woken when latch becomes available

Performance Metrics Analysis

Miss Percentage

-- Good: < 1% for most latches
-- Acceptable: 1-3% depending on latch type
-- Poor: > 5% indicates significant contention
-- Critical: > 10% suggests severe bottleneck

Spin Get Percentage

-- Good: > 80% (most misses resolved by spinning)
-- Acceptable: 60-80%
-- Poor: < 60% (too many sleeps)
-- Indicates spinning effectiveness

Sleep Percentage

-- Good: < 20% of misses result in sleeps
-- Acceptable: 20-40%
-- Poor: > 40% (excessive sleeping)
-- Critical: > 60% (severe contention)

Common Latch Types

Library Cache Latches

-- Library cache latch contention:
-- Symptoms: High miss rates, frequent sleeps
-- Causes: Hard parsing, shared pool fragmentation
-- Solutions: Increase shared pool, use bind variables
-- Pattern: 'library cache%'

Shared Pool Latches

-- Shared pool latch contention:
-- Symptoms: High gets per second, misses during allocation
-- Causes: Frequent memory allocation/deallocation
-- Solutions: Tune shared pool size, reduce hard parsing
-- Pattern: 'shared pool%'

Cache Buffer Chains

-- Buffer cache latch contention:
-- Symptoms: High contention on specific chains
-- Causes: Hot blocks, poorly distributed I/O
-- Solutions: Reduce logical I/O, improve SQL efficiency
-- Pattern: 'cache buffers chains%'

Redo Allocation

-- Redo allocation latch contention:
-- Symptoms: High contention during redo generation
-- Causes: High redo generation rate, small redo logs
-- Solutions: Increase redo log size, reduce redo generation
-- Pattern: 'redo allocation%'

Performance Analysis

Trend Analysis

Identifying Patterns

-- Look for trends in the output:
-- Increasing miss rates over time
-- Peak contention periods
-- Correlation with business activity
-- Seasonal patterns

Comparative Analysis

-- Compare different time periods:
-- Peak vs. off-peak hours
-- Weekday vs. weekend patterns
-- Before vs. after changes
-- Growth trends over months

Contention Hotspots

Time-Based Analysis

-- Identify contention peaks:
-- Specific hours with high miss rates
-- Days with unusual patterns
-- Correlation with batch jobs
-- User activity patterns

Threshold Monitoring

-- Set monitoring thresholds:
-- Miss percentage > 3%
-- Sleep percentage > 25%
-- Gets per second > baseline + 50%
-- Unusual pattern detection

Troubleshooting

High Miss Rates

1. Library Cache Issues

   -- Symptoms: library cache latch misses > 2%
   -- Check: Hard parse rates, shared pool sizing
   -- Solutions: Increase shared_pool_size, use bind variables
   -- Monitor: Cursor sharing effectiveness

2. Shared Pool Contention

   -- Symptoms: shared pool latch high contention
   -- Check: Shared pool free memory, allocation patterns
   -- Solutions: Tune shared pool, reduce fragmentation
   -- Monitor: Shared pool advisor recommendations

Excessive Sleeping

1. Severe Contention

   -- Symptoms: Sleep percentage > 30%
   -- Investigate: Application design issues
   -- Check: Concurrent user activity
   -- Solutions: Application tuning, workload distribution

2. Resource Bottlenecks

   -- Symptoms: Multiple latch types showing high sleeps
   -- Check: CPU utilization, memory pressure
   -- Solutions: Hardware upgrades, workload balancing
   -- Monitor: System resource utilization

Spin Efficiency Issues

1. Poor Spin Success

   -- Symptoms: Spin get percentage < 60%
   -- Indicates: Latch held for long periods
   -- Check: Long-running operations holding latches
   -- Solutions: Optimize operations, reduce hold times

Best Practices

Regular Monitoring

1. Baseline Establishment

   -- Establish performance baselines:
   -- Normal miss rates for each latch type
   -- Typical gets per second during different periods
   -- Expected sleep percentages
   -- Seasonal variation patterns

2. Trend Tracking

   -- Monitor trends over time:
   -- Weekly performance summaries
   -- Monthly trend analysis
   -- Growth pattern identification
   -- Performance degradation detection

Proactive Analysis

1. Threshold Alerting

   -- Set up alerts for:
   -- Miss rates exceeding baselines
   -- Unusual contention patterns
   -- Performance degradation trends
   -- Capacity thresholds

2. Regular Reviews

   -- Periodic analysis:
   -- Weekly latch performance reviews
   -- Monthly capacity planning
   -- Quarterly trend analysis
   -- Annual baseline updates

Related Scripts

• Real-time Latch Analysis (vlatch.sql) - Current latch statistics
• Latch Summary (latchsum.sql) - Latch summary analysis
• Cache Buffer Chains (gvlatch.sql) - RAC latch analysis
• System Wait Events (gvsysev.sql) - Overall wait event analysis

Advanced Usage

Multiple Latch Analysis

-- Analyze multiple related latches:
@platch.sql
-- Enter value for latch_name: %cache%
-- Covers all cache-related latches

-- Compare before/after changes:
-- Run for period before change
-- Run for equivalent period after change
-- Compare miss rates and patterns

Correlation Analysis

-- Correlate with other performance metrics:
-- CPU utilization during high latch contention
-- I/O patterns and buffer cache latch misses
-- Memory pressure and shared pool latches
-- User activity and library cache contention

Notes

• Requires StatsPack to be installed and configured
• Delta calculations between consecutive snapshots provide accurate rates
• Uses optimized join with hint for performance
• Handles multiple instances in RAC environments
• Supports wildcard pattern matching for latch names
• Automatically handles division by zero conditions
• Time-based filtering allows focused analysis periods