Streams Capture Lag History (streams_capture_lag.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 historical analysis of Oracle Streams capture processes by:

• Retrieving capture statistics from AWR history
• Showing message creation and enqueue rates
• Displaying capture lag in seconds
• Breaking down elapsed time by operation type
• Focusing on the last 24 hours of data

Script

column total_messages_created HEADING 'Total|Messages|Created'
column total_messages_enqueued HEADING 'Total Messages|Enqueued'
column lag  format 999,999 HEADING 'Capture|Lag|(Seconds)'
column elapsed_capture HEADING 'Elapsed Time|Capture|(centisecs'
column elapsed_rule_time HEADING 'Elapsed Time|Rule Evaluation|(centisecs)'
column elapsed_enqueue_time HEADING 'Elapsed Time|Enqueuing Messages|(centisecs)'
column elapsed_lcr HEADING 'Elapsed Time|LCR Creation|(centisecs)'
column elapsed_redo_wait_time HEADING 'Elapsed Time|Redo Wait|(centisecs)'
column elapsed_Pause_time HEADING 'Elapsed Time|Paused|(centisecs)'
column begin_interval_time format a27
column end_interval_time format a27
column snap_id format 9,999
column dbid format 9999999999
column instance_number format 999
column capture_name format a16
column total_messages_captured heading 'Messages|Captured'

select s.begin_interval_time,s.end_interval_time , sc.* from dba_hist_streams_capture sc, dba_hist_snapshot s where sc.snap_id=s.snap_id and s.end_interval_time >= systimestamp-1 order by sc.capture_name,s.end_interval_time;

Usage

SQL> @streams_capture_lag.sql

Required Privileges

• SELECT on DBA_HIST_STREAMS_CAPTURE
• SELECT on DBA_HIST_SNAPSHOT
• AWR data must be available

Sample Output

                                                                                 Messages  Capture
BEGIN_INTERVAL_TIME         END_INTERVAL_TIME           SNAP_ID        DBID INSTANCE CAPTURE_NAME     Captured      Lag
--------------------------- --------------------------- ------- ----------- -------- ---------------- -------- --------
15-JAN-24 01:00:22.123      15-JAN-24 02:00:25.456       1001  1234567890        1 STREAMS_CAPTURE    45,678      123
15-JAN-24 02:00:25.456      15-JAN-24 03:00:28.789       1002  1234567890        1 STREAMS_CAPTURE    52,345      156
15-JAN-24 03:00:28.789      15-JAN-24 04:00:31.012       1003  1234567890        1 STREAMS_CAPTURE    48,901       89

Key Output Columns

• BEGIN/END_INTERVAL_TIME: AWR snapshot time window
• SNAP_ID: AWR snapshot identifier
• CAPTURE_NAME: Name of the capture process
• Messages Captured: Total messages captured in interval
• Capture Lag (Seconds): Delay between redo generation and capture
• Elapsed Time columns: Breakdown of time spent in different operations

Understanding the Metrics

Capture Lag Thresholds

• Optimal: < 60 seconds
• Acceptable: 60-300 seconds
• Warning: 300-600 seconds
• Critical: > 600 seconds

Performance Indicators

1. Message Rate: Messages captured per interval
2. Redo Wait Time: Time waiting for redo data
3. Rule Evaluation: Time spent evaluating capture rules
4. Enqueue Time: Time to enqueue messages

Common Use Cases

1. Performance Trending

   • Track capture lag over time
   • Identify peak periods
   • Plan capacity requirements

2. Troubleshooting

   • Diagnose increasing lag
   • Find performance bottlenecks
   • Correlate with system events

3. Capacity Planning

   • Analyze message volumes
   • Predict growth patterns
   • Size Streams pool appropriately

Advanced Analysis

Modify Time Range

-- Last 7 days
and s.end_interval_time >= systimestamp-7

-- Specific date range
and s.end_interval_time between
    to_timestamp('2024-01-15 00:00:00','YYYY-MM-DD HH24:MI:SS')
    and to_timestamp('2024-01-16 00:00:00','YYYY-MM-DD HH24:MI:SS')

Additional Metrics

-- Include all elapsed time columns
select s.begin_interval_time,
       s.end_interval_time,
       sc.capture_name,
       sc.total_messages_captured,
       sc.lag,
       sc.elapsed_capture_time,
       sc.elapsed_rule_time,
       sc.elapsed_enqueue_time,
       sc.elapsed_lcr_time,
       sc.elapsed_redo_wait_time,
       sc.elapsed_pause_time
from dba_hist_streams_capture sc, dba_hist_snapshot s
where sc.snap_id=s.snap_id
order by sc.capture_name,s.end_interval_time;

Troubleshooting High Lag

1. Check Capture State

   SELECT capture_name, state, error_number, error_message
   FROM dba_capture
   WHERE capture_name = 'STREAMS_CAPTURE';

2. Review LogMiner Sessions

   SELECT session_id, used_memory, max_memory
   FROM v$logmnr_session;

3. Analyze Redo Generation Rate

   SELECT to_char(first_time,'YYYY-MM-DD HH24') hour,
          count(*) log_switches,
          round(sum(blocks*block_size)/1024/1024) mb
   FROM v$archived_log
   WHERE first_time > sysdate-1
   GROUP BY to_char(first_time,'YYYY-MM-DD HH24')
   ORDER BY 1;

Best Practices

1. Monitoring

   • Set alerts for lag > 300 seconds
   • Track message rate trends
   • Monitor Streams pool usage

2. Performance Tuning

   • Optimize capture rules
   • Size Streams pool appropriately
   • Consider parallelism settings

3. Maintenance

   • Regular statistics gathering
   • Periodic capture restart
   • Archive old processed logs

Related Scripts

• Streams Apply Lag
• Capture Latency
• Queue Analysis