Streams Capture Latency (capture_latency.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 monitors Oracle Streams capture process performance and latency:

• Measures capture latency in seconds
• Shows message capture and enqueue statistics
• Displays LogMiner session information
• Tracks capture process efficiency
• Identifies potential replication delays

The Script

select c.CAPTURE# CAPTURENUM, c.CAPTURE_NAME,
       nvl((c.CAPTURE_TIME - c.CAPTURE_MESSAGE_CREATE_TIME)*86400, -1) CAPTURE_LATENCY,
       nvl(c.TOTAL_MESSAGES_CAPTURED,0) TOTAL_MESSAGES_CAPTURED,
       nvl(c.CAPTURE_MESSAGE_NUMBER,0) CAPTURE_MESSAGE_NUMBER,
       nvl(c.TOTAL_MESSAGES_ENQUEUED,0) TOTAL_MESSAGES_ENQUEUED,
       nvl(c.ENQUEUE_MESSAGE_NUMBER,0) ENQUEUE_MESSAGE_NUMBER,
       l.read_scn, c.ELAPSED_PAUSE_TIME
from v$streams_capture c, v$logmnr_session l
where c.capture_name = l.session_name
order by 1;

Usage

-- Basic usage
@capture_latency.sql

Required Privileges

SELECT ON V$STREAMS_CAPTURE
SELECT ON V$LOGMNR_SESSION

Sample Output

CAPTURENUM CAPTURE_NAME           CAPTURE_LATENCY TOTAL_MESSAGES_CAPTURED CAPTURE_MESSAGE_NUMBER TOTAL_MESSAGES_ENQUEUED ENQUEUE_MESSAGE_NUMBER    READ_SCN ELAPSED_PAUSE_TIME
---------- -------------------- --------------- ----------------------- ---------------------- ----------------------- ---------------------- ----------- ------------------
         1 CAPTURE_HR                      2.5                  123456                 123456                  123400                     123400   45678901                  0
         2 CAPTURE_SALES                   1.2                   78901                  78901                   78890                      78890   45678902                  5

Key Output Columns

• CAPTURENUM - Capture process number
• CAPTURE_NAME - Name of the capture process
• CAPTURE_LATENCY - Latency in seconds between message creation and capture
• TOTAL_MESSAGES_CAPTURED - Total messages captured since startup
• CAPTURE_MESSAGE_NUMBER - Current capture message sequence
• TOTAL_MESSAGES_ENQUEUED - Total messages enqueued
• ENQUEUE_MESSAGE_NUMBER - Current enqueue message sequence
• READ_SCN - LogMiner read SCN position
• ELAPSED_PAUSE_TIME - Time capture has been paused (seconds)

Understanding the Metrics

Capture Latency:

• < 5 seconds - Excellent performance
• 5-30 seconds - Acceptable for most environments
• > 30 seconds - May indicate performance issues
• -1 - No messages or timing unavailable

Message Processing:

• CAPTURED vs ENQUEUED - Should be very close
• Large difference - Indicates queuing bottleneck
• Zero values - Capture may not be active

Common Use Cases

Replication Monitoring

@capture_latency.sql
-- Monitor data replication latency
-- Ensure timely change capture

Performance Troubleshooting

@capture_latency.sql
-- Identify slow capture processes
-- Check for capture bottlenecks

Capacity Planning

@capture_latency.sql
-- Monitor message throughput
-- Plan for peak load handling

Performance Analysis

High Latency Causes:

1. Heavy DML Load - Too many changes to process
2. LogMiner Performance - Slow redo log reading
3. Network Issues - Slow communication with apply sites
4. Resource Constraints - CPU or I/O limitations

Optimization Strategies:

1. Tune Capture Parameters - Adjust parallelism and memory
2. Optimize Redo Logs - Ensure fast redo log access
3. Monitor Apply Processes - Ensure downstream isn’t backing up
4. Review Filtering Rules - Capture only necessary changes

Troubleshooting

No Output:

• Verify Streams is configured and running
• Check if capture processes are started
• Ensure proper privileges

High Latency:

• Check system resources (CPU, I/O)
• Review redo log performance
• Monitor apply process performance
• Consider capture process tuning

Message Count Discrepancies:

• Check for capture process errors
• Review Streams error queue
• Verify network connectivity

Related Scripts

• Streams Apply Lag - Monitor apply process latency
• Database Health Check - Overall system health
• I/O Statistics - System I/O performance