Streams Apply Lag Monitor (streams_apply_lag.sql)

What This Script Does

This script monitors Oracle Streams apply process performance by analyzing historical statistics from AWR snapshots. It tracks apply lag, message processing rates, transaction handling, and coordinator/server metrics to help diagnose replication issues and optimize Streams performance.

Script

col reader_total_messages_dequeued format 99,999,999 HEADING 'Reader|Tot|Tot|Msgs|Dequeued'
col reader_lag HEADING 'Reader|Lag|(Seconds)'
col coord_total_received HEADING 'Coordinator|Total Txn|Received'
col coord_total_applied HEADING 'Coordinator|Total Txn|Applied'
col coord_total_rollbacks HEADING 'Coordinator|Total Txn|Rollbacks'
col coord_total_wait_deps HEADING 'Coordinator|Total Txn|Wait-Dep'
col coord_total_wait_cmts HEADING 'Coordinator|Total Txn|Wait-Cmt'
col coord_lwm_lag HEADING 'Coordinator|LWM Lag|(seconds)'
col server_total_messages_applied HEADING 'Server|Total Msgs|Applied'
col server_elapsed_dequeue_time HEADING 'Server|Elapsed Dequeue|Time (cs)'
col server_elapsed_apply_time HEADING 'Server|Elapsed Apply|Time (cs)'
column instance_number format 999 heading 'Inst'

select s.begin_interval_time,s.end_interval_time , sa.* from dba_hist_streams_apply_sum sa, dba_hist_snapshot s where sa.snap_id=s.snap_id and s.end_interval_time >= systimestamp-4733 order by
sa.apply_name,s.end_interval_time;

Usage

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

-- No parameters required
-- Shows last 4733 days of Streams apply statistics
-- Modify the time filter as needed: systimestamp-N

Required Privileges

SELECT on DBA_HIST_STREAMS_APPLY_SUM
SELECT on DBA_HIST_SNAPSHOT
Typically requires DBA role or specific AWR privileges

Sample Output

BEGIN_INTERVAL_TIME     END_INTERVAL_TIME       APPLY_NAME     Inst Reader Reader Coordinator Coordinator Coordinator Coordinator Coordinator Coordinator Server   Server         Server
                                                              Tot  Lag    Total Txn   Total Txn   Total Txn   Total Txn   Total Txn   LWM Lag     Total Msgs Elapsed Dequeue Elapsed Apply
                                                              Tot  (Secs) Received    Applied     Rollbacks   Wait-Dep    Wait-Cmt    (seconds)   Applied    Time (cs)       Time (cs)
                                                              Msgs
                                                              Dequeued
----------------------- ----------------------- -------------- ---- ------- ----------- ----------- ----------- ----------- ----------- ----------- ---------- --------------- -------------
15-DEC-23 10:00:00      15-DEC-23 11:00:00     APPLY_SALES       1   12345      567890      567845           5          15          25          45      234567           12345         98765
15-DEC-23 11:00:00      15-DEC-23 12:00:00     APPLY_SALES       1   13456      578901      578856           8          20          35          55      245678           13456        109876
15-DEC-23 12:00:00      15-DEC-23 13:00:00     APPLY_SALES       1   14567      589012      588967          12          25          45          65      256789           14567        120987

Key Output Columns

Time and Identification

BEGIN_INTERVAL_TIME: Snapshot start time
END_INTERVAL_TIME: Snapshot end time
APPLY_NAME: Name of the Streams apply process
Inst: Oracle instance number

Reader Statistics

Reader Tot Msgs Dequeued: Total messages read from queue
Reader Lag (Seconds): Reader lag behind capture process

Coordinator Statistics

Coordinator Total Txn Received: Total transactions received
Coordinator Total Txn Applied: Total transactions successfully applied
Coordinator Total Txn Rollbacks: Total transaction rollbacks
Coordinator Total Txn Wait-Dep: Transactions waiting for dependencies
Coordinator Total Txn Wait-Cmt: Transactions waiting for commits
Coordinator LWM Lag (seconds): Low Water Mark lag in seconds

Server Statistics

Server Total Msgs Applied: Total messages applied by servers
Server Elapsed Dequeue Time (cs): Time spent dequeuing (centiseconds)
Server Elapsed Apply Time (cs): Time spent applying changes (centiseconds)

Understanding Streams Apply Metrics

Lag Analysis

Reader Lag: Indicates capture-to-apply delay
LWM Lag: Shows overall replication lag
Growing lag trends: Performance degradation or load issues

Transaction Processing

Applied vs Received: Success rate of transaction application
Rollback percentage: Error rate indicator
Wait statistics: Dependency and commit bottlenecks

Performance Indicators

Messages/Time ratios: Processing throughput
Dequeue vs Apply time: Time distribution analysis
Growing processing times: Performance degradation

Performance Analysis

Healthy Apply Process Indicators

Stable low lag: Reader and LWM lag under acceptable thresholds
High apply success: Applied transactions close to received
Low rollback rate: Minimal transaction rollbacks
Consistent processing times: Stable dequeue and apply times

Performance Issues

Increasing lag: Growing reader or LWM lag values
High wait counts: Dependency or commit bottlenecks
High rollback rates: Data conflicts or errors
Increasing processing times: Performance degradation

Common Use Cases

Replication Health Monitoring

-- Regular monitoring of apply lag
@streams_apply_lag.sql
-- Focus on lag trends and processing rates

Performance Troubleshooting

-- During replication issues
@streams_apply_lag.sql
-- Analyze transaction success rates and timing

Capacity Planning

-- Historical analysis for growth planning
@streams_apply_lag.sql
-- Review processing capacity trends

Troubleshooting Streams Apply Issues

High Apply Lag

Check apply process status:

SELECT apply_name, status, error_number, error_message
FROM dba_apply;

Analyze bottlenecks:

SELECT apply_name, process_name, state,
       total_messages_applied, total_messages_dequeued
FROM v$streams_apply_server;

High Rollback Rates

Check apply errors:

SELECT apply_name, source_database, error_number,
       error_message, message_count
FROM dba_apply_error;

Review conflict resolution:

SELECT object_owner, object_name, method_name,
       resolution_column, apply_name
FROM dba_apply_conflict_columns;

Low Processing Rates

Adjust parallelism:

BEGIN
  DBMS_APPLY_ADM.SET_PARAMETER(
    apply_name => 'APPLY_SALES',
    parameter  => 'parallelism',
    value      => '4'
  );
END;
/

Monitor server processes:

SELECT apply_name, server_id, state,
       total_messages_applied, elapsed_apply_time
FROM v$streams_apply_server
ORDER BY apply_name, server_id;

Memory and Resource Issues

Check SGA allocation:

SELECT component, current_size/1024/1024 size_mb
FROM v$sga_dynamic_components
WHERE component LIKE '%Streams%';

Adjust memory parameters:

BEGIN
  DBMS_APPLY_ADM.SET_PARAMETER(
    apply_name => 'APPLY_SALES',
    parameter  => 'max_sga_size',
    value      => '100M'
  );
END;
/

Advanced Analysis

Calculate Processing Rates

-- Processing rate analysis
SELECT apply_name,
       end_interval_time,
       coord_total_applied,
       LAG(coord_total_applied) OVER (
         PARTITION BY apply_name
         ORDER BY end_interval_time
       ) prev_applied,
       (coord_total_applied - LAG(coord_total_applied) OVER (
         PARTITION BY apply_name
         ORDER BY end_interval_time
       )) / 3600 txn_per_hour
FROM (/* your query results */)
WHERE apply_name = 'YOUR_APPLY_NAME';

Historical Trend Analysis

-- Lag trend analysis over time
SELECT apply_name,
       TRUNC(end_interval_time, 'DD') day,
       AVG(coord_lwm_lag) avg_lag,
       MAX(coord_lwm_lag) max_lag,
       MIN(coord_lwm_lag) min_lag
FROM (/* your query results */)
GROUP BY apply_name, TRUNC(end_interval_time, 'DD')
ORDER BY apply_name, day;

Streams Capture Latency (capture_latency.sql) - Capture process monitoring
Apply Lag Analysis (apply_lag.sql) - Alternative apply lag monitoring
Active Session Analysis (gvsess.md) - Session-level Streams activity
Queue Analysis (gvaq.md) - Streams queue monitoring