Buffered Queue Statistics (queue_messaging.sql)

What This Script Does

This script monitors Oracle Advanced Queuing (AQ) buffered queues:

Shows all configured buffered queues
Displays current message counts in memory and disk
Monitors queue memory usage and spill behavior
Essential for AQ performance monitoring
Helps identify queue bottlenecks

The Script

select queue_id, QUEUE_NAME, QUEUE_SCHEMA, nvl(cnum_msgs,0) CNUM_MSGS,
       nvl(cspill_msgs,0) CSPILL_MSGS, nvl(num_msgs,0) num_msgs
from v$buffered_queues;

Usage

-- Basic usage
@queue_messaging.sql

Required Privileges

SELECT ON V$BUFFERED_QUEUES

Sample Output

 QUEUE_ID QUEUE_NAME           QUEUE_SCHEMA CNUM_MSGS CSPILL_MSGS   NUM_MSGS
--------- -------------------- ------------ --------- ----------- ----------
        1 ORDER_QUEUE          APP_SCHEMA         125           0        125
        2 NOTIFICATION_QUEUE   MSG_SCHEMA          45          15         60
        3 ERROR_QUEUE          ADMIN_SCHEMA         0           0          0
        4 BATCH_QUEUE          ETL_SCHEMA         500         200        700

Key Output Columns

QUEUE_ID - Internal queue identifier
QUEUE_NAME - Name of the queue
QUEUE_SCHEMA - Schema owning the queue
CNUM_MSGS - Current messages in memory buffers
CSPILL_MSGS - Messages spilled to disk due to memory limits
NUM_MSGS - Total messages (memory + disk)

Understanding the Metrics

Memory vs Disk Storage:

CNUM_MSGS - Messages in SGA memory (fastest access)
CSPILL_MSGS - Messages moved to disk (slower access)
NUM_MSGS - Total queue depth (all messages)

Performance Indicators:

High CNUM_MSGS - Good performance, messages in memory
High CSPILL_MSGS - May indicate memory pressure
Large NUM_MSGS - Potential queue backlog

Common Use Cases

Queue Performance Monitoring

@queue_messaging.sql
-- Monitor queue depths
-- Check for message backlogs

Memory Usage Analysis

@queue_messaging.sql
-- Identify queues spilling to disk
-- Monitor memory efficiency

Capacity Planning

@queue_messaging.sql
-- Track queue growth patterns
-- Plan memory allocation

Troubleshooting

@queue_messaging.sql
-- Identify problematic queues
-- Check for processing bottlenecks

Performance Analysis

Optimal Conditions:

Most messages in CNUM_MSGS (memory)
Low or zero CSPILL_MSGS
Steady, low NUM_MSGS values

Warning Signs:

High CSPILL_MSGS ratios
Continuously growing NUM_MSGS
Very large queue depths

Memory Pressure Indicators:

-- High spill ratio indicates memory issues
-- CSPILL_MSGS / NUM_MSGS > 0.2 (20%) may need attention

Optimization Strategies

Increase Memory Allocation:

-- Adjust STREAMS_POOL_SIZE parameter
ALTER SYSTEM SET STREAMS_POOL_SIZE = 512M;

-- Or increase SGA_TARGET for automatic management
ALTER SYSTEM SET SGA_TARGET = 2G;

Tune Queue Parameters:

-- Increase max_retry_delay
BEGIN
  DBMS_AQADM.ALTER_QUEUE(
    queue_name => 'SCHEMA.QUEUE_NAME',
    max_retries => 5,
    retry_delay => 60
  );
END;
/

Improve Processing:

Increase consumer processes
Optimize message processing logic
Consider message batching
Review dequeue patterns

Troubleshooting

No Output:

Verify AQ is configured and enabled
Check if buffered queues exist
Ensure proper privileges

High Spill Messages:

Increase STREAMS_POOL_SIZE
Review message processing efficiency
Consider queue partitioning
Optimize dequeue operations

Growing Message Counts:

Check consumer process health
Review message processing errors
Verify dequeue logic
Monitor system resources

Queue Management Commands

Check Queue Status:

SELECT name, enqueue_enabled, dequeue_enabled
FROM dba_queues
WHERE owner = 'SCHEMA_NAME';

Monitor Queue Activity:

SELECT * FROM v$aq
WHERE qschema = 'SCHEMA_NAME';

Purge Old Messages:

BEGIN
  DBMS_AQADM.PURGE_QUEUE_TABLE(
    queue_table => 'SCHEMA.QUEUE_TABLE',
    purge_condition => 'qtview.enq_time < SYSDATE - 7'
  );
END;
/

System Performance - Overall system metrics
Memory Analysis - Memory usage patterns
Session Activity - Queue processing sessions