Queue Message Receiving Statistics (queue_receiving.sql)

What This Script Does

This script displays statistics for Oracle Streams propagation receivers, showing information about queues receiving messages from remote databases. It provides essential metrics for monitoring message propagation performance, including total messages received, processing times, and source database information. This is crucial for monitoring replication health, troubleshooting propagation issues, and ensuring data consistency across distributed Oracle environments.

The Script

select SRC_QUEUE_NAME, SRC_QUEUE_SCHEMA, TOTAL_MSGS, ELAPSED_ENQUEUE_TIME, SRC_DBNAME
from v$propagation_receiver ;

Key Features

Propagation Monitoring: Shows active message propagation receivers
Message Count Tracking: Displays total messages received per queue
Performance Metrics: Shows elapsed enqueue time for processing
Source Identification: Identifies source databases and queues
Replication Health: Provides insight into replication status
Distributed System Support: Essential for multi-database environments

Usage

@queue_receiving.sql

No parameters required - shows all active propagation receivers.

Required Privileges

SELECT on V$PROPAGATION_RECEIVER

Sample Output

SRC_QUEUE_NAME           SRC_QUEUE_SCHEMA TOTAL_MSGS ELAPSED_ENQUEUE_TIME SRC_DBNAME
------------------------ ---------------- ---------- -------------------- ----------
STRMADMIN_APPLY_Q        STRMADMIN              1234                 5678 SRCDB01
REPLICATION_QUEUE        REP_ADMIN              5678                12345 SRCDB02
STREAMS_QUEUE            STREAMS_USER           2345                 8901 BACKUPDB

Key Output Columns

SRC_QUEUE_NAME: Name of the source queue sending messages
SRC_QUEUE_SCHEMA: Schema owner of the source queue
TOTAL_MSGS: Total number of messages received from this source
ELAPSED_ENQUEUE_TIME: Total time spent enqueuing messages (centiseconds)
SRC_DBNAME: Name of the source database

Understanding Propagation Receivers

Oracle Streams Architecture

Propagation Components

-- Streams propagation involves:
-- Capture process: Captures changes at source
-- Propagation: Transfers messages between databases
-- Apply process: Applies changes at destination
-- Queues: Store messages during propagation

Message Flow

-- Message propagation flow:
-- Source database captures changes
-- Messages queued in source queue
-- Propagation sends messages to destination
-- Destination receives messages into local queue
-- Apply process processes received messages

Receiver Statistics

Message Processing Metrics

-- Key performance indicators:
-- TOTAL_MSGS: Volume of replication activity
-- ELAPSED_ENQUEUE_TIME: Processing efficiency
-- Source identification: Replication topology
-- Queue performance: Processing bottlenecks

Performance Analysis

-- Performance evaluation:
-- Messages per second calculation
-- Average enqueue time per message
-- Throughput trends over time
-- Bottleneck identification

Common Use Cases

Replication Monitoring
- Monitor Oracle Streams replication health
- Track message propagation volumes
- Identify replication lag issues
- Support disaster recovery monitoring
Performance Analysis
- Analyze propagation performance
- Identify processing bottlenecks
- Monitor queue efficiency
- Support capacity planning
Troubleshooting
- Diagnose replication issues
- Identify failed or slow propagations
- Support incident resolution
- Validate replication configuration
Capacity Planning
- Assess replication workload
- Plan infrastructure scaling
- Monitor growth trends
- Support architecture decisions

Advanced Analysis

Performance Calculation

Message Processing Rate

-- Calculate messages per second:
-- Requires baseline measurement over time
-- Monitor TOTAL_MSGS changes between samples
-- Calculate rate: (msgs_now - msgs_before) / time_diff
-- Useful for capacity planning

Enqueue Efficiency

-- Average enqueue time per message:
-- ELAPSED_ENQUEUE_TIME / TOTAL_MSGS
-- Lower values indicate better performance
-- High values suggest processing bottlenecks
-- Useful for performance optimization

Replication Health Assessment

Normal Operation Indicators

-- Healthy replication characteristics:
-- Consistent message flow
-- Reasonable enqueue times
-- No error conditions
-- Expected source databases present

Problem Indicators

-- Potential issues:
-- Stopped message flow (no new messages)
-- Very high enqueue times
-- Missing expected sources
-- Error messages in alert logs

Integration with Streams Monitoring

Comprehensive Streams Analysis

-- Monitor complete Streams environment:
-- V$STREAMS_CAPTURE: Capture process status
-- V$STREAMS_APPLY_COORDINATOR: Apply process status
-- DBA_QUEUE_TABLES: Queue table information
-- DBA_QUEUES: Queue configuration

End-to-End Monitoring

-- Complete replication monitoring:
-- Capture lag at source
-- Propagation efficiency (this script)
-- Apply lag at destination
-- Overall replication health

Performance Correlation

Cross-Component Analysis

-- Correlate with other components:
-- Capture process performance
-- Apply process efficiency
-- Network latency impact
-- Database resource utilization

Troubleshooting Applications

Replication Issues

Common Problems

-- Typical replication issues:
-- Propagation stopped or delayed
-- High processing times
-- Network connectivity problems
-- Queue space limitations

Investigation Steps

-- Troubleshooting approach:
-- Check propagation receiver status
-- Verify network connectivity
-- Review queue space and limits
-- Analyze apply process status
-- Check for errors in alert logs

Performance Problems

Slow Propagation

-- Slow propagation diagnosis:
-- High ELAPSED_ENQUEUE_TIME values
-- Low message throughput
-- Resource contention on destination
-- Network bandwidth limitations

Resolution Strategies

-- Performance improvement:
-- Tune apply process parameters
-- Optimize network configuration
-- Increase queue space if needed
-- Consider parallel apply processes

Best Practices

Regular Monitoring

Monitoring Schedule

-- Regular assessment:
-- Real-time during critical operations
-- Daily health checks
-- Weekly performance reviews
-- Monthly capacity assessments

Baseline Establishment

-- Performance baselines:
-- Normal message volumes
-- Expected processing times
-- Typical source database activity
-- Peak usage characteristics

Performance Optimization

Systematic Approach

-- Optimization methodology:
-- Identify performance bottlenecks
-- Analyze root causes
-- Implement targeted improvements
-- Monitor improvement effectiveness

Preventive Measures

-- Prevention strategies:
-- Proactive monitoring
-- Capacity planning
-- Regular maintenance
-- Architecture reviews

Integration with Monitoring Tools

Automated Monitoring

Alert Configuration

-- Alert criteria:
-- Message flow stopped
-- Processing time thresholds exceeded
-- Unexpected source databases
-- Error conditions detected

Dashboard Integration

-- Dashboard components:
-- Current propagation status
-- Message volume trends
-- Processing time metrics
-- Replication health indicators

Historical Analysis

Trend Tracking

-- Long-term analysis:
-- Message volume growth
-- Performance trends
-- Capacity utilization
-- System evolution

apply_lag.sql - Apply process lag analysis
capture_latency.sql - Capture process latency
streams_apply_lag.sql - Streams apply lag
streams_capture_lag.sql - Streams capture lag

Enhanced Analysis

Extended Monitoring Query

Comprehensive Receiver Analysis

-- Enhanced query with additional metrics:
SELECT
  src_queue_name,
  src_queue_schema,
  total_msgs,
  elapsed_enqueue_time,
  src_dbname,
  ROUND(elapsed_enqueue_time / DECODE(total_msgs, 0, 1, total_msgs), 2) as avg_enqueue_time,
  ROUND(total_msgs / DECODE(elapsed_enqueue_time, 0, 1, elapsed_enqueue_time/100), 2) as msgs_per_sec
FROM v$propagation_receiver
ORDER BY total_msgs DESC;

Historical Tracking

Baseline Comparison

-- Track changes over time:
-- Regular snapshots of receiver statistics
-- Compare current vs. historical performance
-- Identify trends and patterns
-- Support capacity planning

Security Considerations

Access Control

Privilege Management

-- Limit access to authorized personnel:
-- Replication administrators
-- Database monitoring teams
-- Security teams for compliance
-- Audit access when required

Data Sensitivity

-- Consider information sensitivity:
-- Replication topology exposure
-- Database naming conventions
-- Message volume patterns
-- Performance characteristics

Summary

This script is essential for:

Replication Monitoring - Monitoring Oracle Streams and Advanced Queuing propagation health
Performance Analysis - Analyzing message propagation performance and efficiency
Troubleshooting - Diagnosing replication issues and propagation problems
Capacity Planning - Understanding replication workload and planning infrastructure
Distributed System Management - Managing and monitoring distributed Oracle environments