Streams Apply Lag (apply_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 monitors Oracle Streams apply process performance and latency:

• Measures apply process latency in seconds
• Shows message dequeue and apply statistics
• Displays high water mark (HWM) information
• Tracks apply process efficiency
• Identifies replication bottlenecks at the destination

The Script

select ac.apply# applynum, ac.apply_name, ar.TOTAL_MESSAGES_DEQUEUED,
       nvl(ar.DEQUEUED_MESSAGE_NUMBER,0) DEQUEUED_MESSAGE_NUMBER, ac.total_received,
       ac.total_assigned, ac.total_applied, nvl(ac.hwm_message_number,0)
       HWM_MESSAGE_NUMBER,
       nvl((ac.hwm_time - hwm_message_create_time)*86400, -1) HWM_LATENCY,
       nvl((ar.dequeue_time -
            ar.dequeued_message_create_time)*86400, -1) DEQUEUE_LATENCY
from v$streams_apply_coordinator ac,
     v$streams_apply_reader ar
where ac.apply#=ar.apply#
order by 1;

Usage

-- Basic usage
@apply_lag.sql

Required Privileges

SELECT ON V$STREAMS_APPLY_COORDINATOR
SELECT ON V$STREAMS_APPLY_READER

Sample Output

APPLYNUM APPLY_NAME           TOTAL_MESSAGES_DEQUEUED DEQUEUED_MESSAGE_NUMBER TOTAL_RECEIVED TOTAL_ASSIGNED TOTAL_APPLIED HWM_MESSAGE_NUMBER HWM_LATENCY DEQUEUE_LATENCY
-------- -------------------- ----------------------- ----------------------- -------------- -------------- ------------- ------------------ ----------- ---------------
       1 APPLY_HR                                123400                  123400         123400         123400        123398             123400         3.2             2.8
       2 APPLY_SALES                              78890                   78890          78890          78890         78888              78890         1.5             1.1

Key Output Columns

• APPLYNUM - Apply process number
• APPLY_NAME - Name of the apply process
• TOTAL_MESSAGES_DEQUEUED - Total messages read from queue
• DEQUEUED_MESSAGE_NUMBER - Current dequeue message sequence
• TOTAL_RECEIVED - Total messages received by coordinator
• TOTAL_ASSIGNED - Total messages assigned to apply servers
• TOTAL_APPLIED - Total messages successfully applied
• HWM_MESSAGE_NUMBER - High water mark message number
• HWM_LATENCY - High water mark latency in seconds
• DEQUEUE_LATENCY - Current dequeue latency in seconds

Understanding the Metrics

Latency Values:

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

Message Flow:

• DEQUEUED → RECEIVED → ASSIGNED → APPLIED
• All values should be close - Large gaps indicate bottlenecks
• APPLIED < ASSIGNED - Apply servers may be overloaded

Common Use Cases

Replication Monitoring

@apply_lag.sql
-- Monitor destination replication lag
-- Ensure timely change application

Performance Troubleshooting

@apply_lag.sql
-- Identify slow apply processes
-- Check for apply bottlenecks

Load Balancing

@apply_lag.sql
-- Monitor apply process throughput
-- Balance workload across apply servers

Performance Analysis

High Latency Causes:

1. Complex Transactions - Large or complex DML operations
2. Constraint Checking - Slow foreign key or trigger processing
3. Resource Constraints - CPU, memory, or I/O limitations
4. Lock Contention - Conflicts with other database activity

Message Processing Gaps:

• RECEIVED < DEQUEUED - Coordinator bottleneck
• ASSIGNED < RECEIVED - Assignment process slow
• APPLIED < ASSIGNED - Apply servers overloaded

Optimization Strategies

Tune Apply Parameters:

-- Increase parallelism
DBMS_APPLY_ADM.SET_PARAMETER(
  apply_name => 'APPLY_HR',
  parameter  => 'parallelism',
  value      => '4'
);

-- Adjust commit SCN
DBMS_APPLY_ADM.SET_PARAMETER(
  apply_name => 'APPLY_HR',
  parameter  => 'commit_serialization',
  value      => 'NONE'
);

Monitor Resource Usage:

• Check CPU and memory utilization
• Monitor I/O performance
• Review database locks and waits

Troubleshooting

No Output:

• Verify Streams apply processes are configured
• Check if apply processes are started
• Ensure proper privileges

High Latency:

• Check destination database performance
• Review apply process parameters
• Monitor constraint and trigger overhead
• Check for lock contention

Message Count Issues:

• Check for apply process errors
• Review Streams error queue
• Verify conflict resolution rules

Related Scripts

• Streams Capture Latency - Monitor capture process
• Session Activity - Check apply session activity
• Lock Analysis - Identify lock contention