Advanced Queuing Table Status (vaq.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 Advanced Queuing activity by:

• Displaying current queue status and message counts
• Showing waiting, ready, and expired message statistics
• Filtering for queues with active messages only
• Supporting pattern matching for owner and queue name
• Providing wait time statistics for performance analysis
• Joining performance views with queue metadata

Script

rem vaq.sql
rem
tti 'AQ Table Status'
rem
set lines 132
rem
col owner format a9
col name format a30
col queue_table format a30
rem col waiting,
rem col ready,
rem col expired,
rem col total_wait,
rem col average_wait
rem
select
       owner,
       name,
       queue_table,
       waiting,
       ready,
       expired,
       total_wait,
       average_wait
-- from gv$aq g ,
from v$aq g ,
     all_queues d
where g.qid = d.qid
and upper(d.owner) like upper(nvl('&owner', '%'))
and upper(d.name) like upper(nvl('&name', '%'))
and (g.waiting > 0 or g.ready > 0 or g.expired > 0)
order by owner, name
/

Usage

SQL> @vaq.sql
Enter value for owner: SCOTT
Enter value for name: ORDER%

Parameters

• owner: Queue owner pattern (% for all owners, specific name for single owner)
• name: Queue name pattern (% for all queues, specific pattern for filtering)

Required Privileges

• SELECT on V$AQ
• SELECT on ALL_QUEUES

Sample Output

AQ Table Status

OWNER     NAME                           QUEUE_TABLE                      WAITING      READY    EXPIRED TOTAL_WAIT AVERAGE_WAIT
--------- ------------------------------ ------------------------------ --------- ---------- ---------- ---------- ------------
SCOTT     ORDER_QUEUE                    ORDER_QT                              0        125          5        234         45.2
SCOTT     PAYMENT_QUEUE                  PAYMENT_QT                            3         87          2        456         78.5
HR        EMPLOYEE_NOTIFICATIONS         EMP_NOTIF_QT                          0         45          0         89         12.3
SALES     CUSTOMER_UPDATES              CUST_UPD_QT                           1        234         12        567         89.1

Key Output Columns

• OWNER: Schema that owns the queue
• NAME: Queue name
• QUEUE_TABLE: Underlying queue table name
• WAITING: Number of messages waiting to be processed
• READY: Number of messages ready for consumption
• EXPIRED: Number of expired messages
• TOTAL_WAIT: Total wait time for all operations
• AVERAGE_WAIT: Average wait time per operation

Understanding Queue Message States

Message State Definitions

• WAITING: Messages not yet ready for dequeue (future processing)
• READY: Messages available for immediate consumption
• EXPIRED: Messages that exceeded their expiration time
• PROCESSED: Messages successfully consumed (not shown in active count)

Normal vs Problem Patterns

• Healthy Queue: Low waiting, steady ready count, minimal expired
• Backlog: High ready count, increasing over time
• Stalled Processing: High waiting count, low ready processing
• Expiration Issues: High expired count

Queue Performance Analysis

Message Flow Health

-- Extended analysis with retention info
SELECT q.owner, q.name, q.queue_table,
       aq.waiting, aq.ready, aq.expired,
       q.max_retries, q.retry_delay,
       q.retention_time
FROM all_queues q, v$aq aq
WHERE q.qid = aq.qid
AND (aq.waiting > 0 OR aq.ready > 0 OR aq.expired > 0)
ORDER BY (aq.waiting + aq.ready + aq.expired) DESC;

Wait Time Analysis

-- Calculate efficiency metrics
SELECT owner, name,
       CASE
         WHEN total_wait = 0 THEN 0
         ELSE ROUND(average_wait, 2)
       END avg_wait_time,
       CASE
         WHEN average_wait < 10 THEN 'Excellent'
         WHEN average_wait < 50 THEN 'Good'
         WHEN average_wait < 100 THEN 'Fair'
         ELSE 'Poor'
       END performance_rating
FROM v$aq aq, all_queues q
WHERE aq.qid = q.qid
AND (aq.waiting > 0 OR aq.ready > 0 OR aq.expired > 0);

Common Use Cases

1. Queue Monitoring

   • Monitor message backlogs
   • Identify processing bottlenecks
   • Track queue performance trends

2. Troubleshooting

   • Find queues with high expired counts
   • Identify stalled message processing
   • Debug application queue issues

3. Capacity Planning

   • Monitor queue growth patterns
   • Plan consumer scaling
   • Optimize queue configurations

Queue Health Indicators

Healthy Queue Patterns

• Steady Ready Count: Messages processed as they arrive
• Low Wait Times: Quick message processing
• Minimal Expired: Good retention settings
• Balanced Load: No single queue dominating

Problem Indicators

• Growing Ready Count: Insufficient consumers
• High Expired Count: Messages timing out
• Long Average Wait: Processing delays
• Zero Activity: Stalled consumers

Filtering Strategies

By Owner

-- Monitor specific application queues
Enter value for owner: ORDERAPP
Enter value for name: %

By Queue Pattern

-- Monitor notification queues
Enter value for owner: %
Enter value for name: %NOTIFICATION%

By Table Pattern

-- Find queues using specific table naming
SELECT * FROM all_queues
WHERE queue_table LIKE '%ORDER%';

Performance Troubleshooting

High Message Counts

Symptoms: Large ready or waiting counts Causes:

• Insufficient consumers
• Slow message processing
• Consumer failures

Solutions:

• Add more consumers
• Optimize message processing
• Check consumer error logs

High Expired Counts

Symptoms: Many expired messages Causes:

• Short expiration times
• Processing delays
• Consumer downtime

Solutions:

• Increase retention time
• Improve processing speed
• Add consumer redundancy

Long Wait Times

Symptoms: High average_wait values Causes:

• Network issues
• Database performance
• Lock contention

Solutions:

• Check network connectivity
• Tune database performance
• Optimize queue operations

Queue Table Analysis

Message Details

-- Examine messages in queue table
SELECT msg_state, msg_priority, user_data,
       enq_time, deq_time
FROM aq$queue_table_name
WHERE q_name = 'QUEUE_NAME'
ORDER BY enq_time DESC;

Queue Statistics

-- Get detailed queue statistics
SELECT queue_schema, queue_name,
       num_msgs, spill_msgs,
       cnum_msgs, dequeue_enabled,
       enqueue_enabled
FROM dba_queue_tables
WHERE owner = 'SCOTT';

Administrative Tasks

Queue Management

-- Enable/disable queue operations
BEGIN
  DBMS_AQADM.STOP_QUEUE(
    queue_name => 'SCOTT.ORDER_QUEUE'
  );
END;
/

BEGIN
  DBMS_AQADM.START_QUEUE(
    queue_name => 'SCOTT.ORDER_QUEUE'
  );
END;
/

Queue Purging

-- Purge expired messages
BEGIN
  DBMS_AQADM.PURGE_QUEUE_TABLE(
    queue_table => 'SCOTT.ORDER_QT',
    purge_condition => 'tab.q_name = ''ORDER_QUEUE''
                       AND tab.msg_state = ''EXPIRED'''
  );
END;
/

Monitoring Automation

Create Monitoring View

CREATE OR REPLACE VIEW queue_health_monitor AS
SELECT owner, name, queue_table,
       waiting + ready + expired total_messages,
       CASE
         WHEN expired > (waiting + ready) * 0.1 THEN 'HIGH_EXPIRED'
         WHEN ready > 1000 THEN 'HIGH_BACKLOG'
         WHEN average_wait > 100 THEN 'SLOW_PROCESSING'
         ELSE 'HEALTHY'
       END health_status,
       waiting, ready, expired, average_wait
FROM v$aq aq, all_queues q
WHERE aq.qid = q.qid;

Alert Generation

-- Identify problem queues
SELECT * FROM queue_health_monitor
WHERE health_status != 'HEALTHY'
ORDER BY total_messages DESC;

Integration with Applications

Consumer Monitoring

-- Check active consumers
SELECT client_name, consumer_name,
       address, protocol,
       last_error_msg, last_error_date
FROM dba_queue_subscribers
WHERE queue_name = 'ORDER_QUEUE';

Error Analysis

-- Find processing errors
SELECT exception_queue_owner, exception_queue,
       original_queue_owner, original_queue,
       COUNT(*) error_count
FROM dba_queue_tables qt, aq$qt.aq$_qt_e e
WHERE qt.queue_table = 'ORDER_QT'
GROUP BY exception_queue_owner, exception_queue,
         original_queue_owner, original_queue;

RAC Considerations

Cross-Instance Queues

For RAC environments, consider using GV$AQ:

-- Monitor across all instances
SELECT inst_id, owner, name,
       waiting, ready, expired
FROM (
  SELECT g.inst_id, d.owner, d.name,
         g.waiting, g.ready, g.expired
  FROM gv$aq g, all_queues d
  WHERE g.qid = d.qid
  AND (g.waiting > 0 OR g.ready > 0 OR g.expired > 0)
)
ORDER BY inst_id, owner, name;

Best Practices

1. Regular Monitoring

   • Check queue status hourly during peak times
   • Monitor message expiration rates
   • Track average wait time trends

2. Proactive Management

   • Set up alerts for high backlogs
   • Monitor expired message trends
   • Plan consumer capacity

3. Performance Optimization

   • Tune queue table storage
   • Optimize message processing
   • Balance consumer workload

Troubleshooting

No Queues Shown

• Check if AQ is configured
• Verify queue activity (script filters inactive queues)
• Confirm user privileges

Missing Statistics

• Restart queue activity to refresh stats
• Check if statistics collection is enabled
• Verify queue configuration

Performance Issues

• Analyze message processing logic
• Check database performance
• Review network connectivity

Related Scripts

• Database Link Monitoring
• Session Analysis
• Lock Analysis
• Queue Message Analysis