Managing Message Priority and Failures in Oracle Advanced Queuing

The Danger of the "First-In, First-Out" Trap

In many asynchronous systems, a surge of low-priority background tasks—like report generation or email notifications—can clog the pipeline, delaying critical real-time operations like payment processing or security alerts. If your messaging layer treats every request as equal, your most important business processes are at the mercy of your least important ones.

Oracle Advanced Queuing (AQ) solves this by allowing messages to be prioritized and providing a mechanism to isolate "poison messages" that repeatedly fail processing. However, implementing this requires a specific configuration of the queue table's sort list and a strategy for handling the exception queue.

Defining Priority via the Sort List

Unlike simple queues, Oracle AQ determines the order of dequeuing based on a SORT_LIST defined at the queue table level. By default, AQ uses the enqueue time. To support priority, you must explicitly include PRIORITY in the sort list during the creation of the queue table.

When a message is enqueued, a priority value (typically 0 to 100, where lower numbers often indicate higher priority depending on the sort order) is assigned. The database uses the sort list to order the messages in the underlying table, ensuring the consumer receives the most urgent items first regardless of when they arrived.

Handling Failures with Exception Queues

A "poison message" is a payload that causes the consumer application to crash or roll back every time it is processed. Without a safety valve, this message would stay at the head of the queue, blocking all subsequent messages and creating an infinite loop of failures.

Oracle AQ handles this via the Exception Queue. When a message exceeds the MAX_RETRIES limit defined for the queue, Oracle automatically moves it to an exception queue (usually named AQ$__E). This removes the blocker from the main pipeline while preserving the failed message and its metadata for manual inspection and recovery.

Worked Example: Priority Queue Implementation

The following example demonstrates how to create a priority-aware queue and enqueue messages with different priority levels. This requires a user with AQ_ADMINISTRATOR_ROLE or DBMS_AQADM execution privileges.

-- Run as a user with AQ administrative privileges
-- 1. Create a payload type
CREATE OR REPLACE TYPE my_msg_type AS OBJECT (
  msg_id NUMBER,
  text VARCHAR2(100)
);
/

-- 2. Create the queue table with PRIORITY in the sort list
BEGIN
  DBMS_AQADM.CREATE_QUEUE_TABLE(
    queue_table        => 'priority_q_table',
    queue_payload_type => 'my_msg_type',
    sort_list          => 'PRIORITY, ENQ_TIME'
  );
END;
/

-- 3. Create and start the queue
BEGIN
  DBMS_AQADM.CREATE_QUEUE('priority_queue', 'priority_q_table');
  DBMS_AQADM.START_QUEUE('priority_queue');
END;
/

-- 4. Enqueue messages with different priorities
DECLARE
  enqueue_options    DBMS_AQ.ENQUEUE_OPTIONS_T;
  message_properties  DBMS_AQ.MESSAGE_PROPERTIES_T;
  message_handle     RAW(16);
  payload            my_msg_type;
BEGIN
  -- Low priority message (Priority 10)
  payload := my_msg_type(1, 'Low priority task');
  message_properties.priority := 10;
  DBMS_AQ.ENQUEUE('priority_queue', enqueue_options, message_properties, payload, message_handle);

  -- High priority message (Priority 1)
  payload := my_msg_type(2, 'Urgent task');
  message_properties.priority := 1;
  DBMS_AQ.ENQUEUE('priority_queue', enqueue_options, message_properties, payload, message_handle);
  
  COMMIT;
END;
/

Verification of Result

To verify that the priority is working, perform a dequeue operation. Despite the "Low priority task" being enqueued first, the consumer will receive the "Urgent task" first because the SORT_LIST is configured as 'PRIORITY, ENQ_TIME'.

Monitoring and Limitations

You can monitor the state of your queues using the DBA_QUEUES and DBA_QUEUE_TABLES views. To check for failed messages, query the exception queue table associated with your main queue table.

Trade-offs and Constraints

• Performance Overhead: Using a complex SORT_LIST adds a small amount of overhead to the enqueue and dequeue process compared to a simple FIFO queue.
• Payload Size: Large payloads stored directly in the queue table can lead to fragmentation and slower performance. For very large messages, consider storing a reference (pointer) in the AQ payload and the actual data in a separate LOB table.
• Retry Logic: If MAX_RETRIES is set too high, poison messages will block the queue for longer periods before being moved to the exception queue.

Actionable Summary

1. Set the Sort List: Always include PRIORITY in DBMS_AQADM.CREATE_QUEUE_TABLE if you need to bypass FIFO ordering.
2. Define Priority Ranges: Establish a clear internal standard (e.g., 1 = Critical, 10 = Routine) to avoid inconsistent priority assignments across different applications.
3. Monitor Exception Queues: Set up alerts or daily checks on the AQ$__E tables to ensure failed messages are analyzed and re-processed or purged.
4. Tune Retries: Balance MAX_RETRIES to allow for transient network glitches while ensuring poison messages are exiled quickly.