Oracle Advanced Queue: A Guide

In this article, we'll look at what Oracle advanced queue is, explore some of the use cases, and consider common best practices while implementing queue.

Brief Overview of Oracle Advanced Queue (AQ)

In a business application, many systems and apps work together to integrate and exchange information. Oracle Advanced Queuing allows asynchronous communication by sending and receiving the message. This enables the message to be seamlessly integrated and stored at the database structure level. 

High availability, scalability, and reliability are all operational benefits of Oracle Advanced Queuing. As the information is stored at the database level DBAs and developers have full control over the message and its life cycle.

Importance of Messaging Systems in Enterprise Applications

Enterprise apps are typically large and intricate programs that are unique to the organization. This requires a high degree of security and scalability. Typically, in-house and externally acquired products exchange information with each other to achieve the intended business objective. Oracle Advanced queue enables the message to be propagated in a reliable and secure manner, as it inherits the messaging standards, messages are never lost, as the messages are stored at the local database level.  This particularly comes in handy, when the apps communicate outside of an organization's network to exchange and store the information. 

Article Objectives and Scope

This article aims to grasp the concept of Oracle Advanced Queuing, its advantages, and the approach to its implementation. Upon completing this article, the reader should be equipped to comprehend, apply, and convey the advantages of Oracle Advanced Queuing.

What Is Oracle Advanced Queue?

Oracle Advanced Queuing (AQ) is a message-oriented middleware that is integrated into Oracle Database. It uses the capabilities of the Oracle database to keep messages in persistent queues. It is primarily designed for managing application processes, handling small services, and executing actions in response to specific events. 

History of Oracle AQ

• Oracle first included an advanced queue as a built-in feature in release 8i.
• Oracle 9i further enhanced AQ by improving performance, message expiration maintenance, and prioritizing messages in the queue.
• XML message support, message transformation, and monitoring features were all included in Oracle 10g.
• Multitenant architecture and security features were enhanced in Oracle 12c

Key Features and Benefits of Oracle AQ

In a nutshell, high availability, scalability, reliability, and traceability are the main features of queue data. We will look at these features in depth:

• Oracle queues have been engineered to be both scalable and highly performant, enabling it to effectively manage massive message volumes (CLOB/ RAW data) and facilitate concurrent message processing. It makes use of parallel processing and partitioning, two scalability features of Oracle Database, to meet the demands of demanding enterprise environments.
• AQ queue messages are dependable and long-lasting. Because they are stored in the database, even in the case of system crashes or failures, they are not lost. For mission-critical applications where data integrity is vital, this dependability is essential.
• It allows messages to be exchanged between applications or between different areas of an application without requiring both parties to be available or actively present at the same time.
• AQ offers the ability to maintain message ordering inside a queue, guaranteeing that messages are handled in the order that they are queued. For applications where a precise order of operations is required, this feature is crucial (FIFO – First In First Out).
• Oracle's advanced queue can handle a wide range of message types, such as text, XML, raw data, and user-defined types. Because of this adaptability, developers can integrate AQ with a variety of application architectures and work with a variety of data types.
• Messages can be assigned varying levels of priority thanks to AQ's support for priority-based message queuing. Because of this feature, complex message processing logic based on message priority can be implemented.
• Monitoring and management: Through Oracle Enterprise Manager (OEM), a number of database views, and APIs, AQ offers extensive monitoring and management capabilities. Developers can easily manage queue configurations, track message processing metrics, and keep an eye on queue status.
• AQ is engineered to be both scalable and highly performant, enabling it to effectively manage massive message volumes and facilitate concurrent message processing. It makes use of parallel processing and partitioning, two scalability features of Oracle Database, to meet the demands of demanding enterprise environments.

Comparison With Other Messaging Systems (e.g., JMS, MSMQ)

There are other similar messaging platforms available in the Market namely JMS (Jave Messaging System and Microsoft Message Queue). As a matter of comparison, we will see the primary differences between them. This shall help architects and developers in selecting or recommending the most correct platform.

Message Model explanation via diagram

Selecting between Oracle AQ, JMS, and MSMQ depends on the company's current technology framework, the need for scalability, the setting for deployment, and the anticipated need for future integration. Every messaging solution offers unique advantages and is appropriate for various situations, considering these elements.

Architecture and Components

So far, we have understood, what is oracle AQ, its history, benefits, and a comparison with similar messaging systems or platforms. From here onwards, we will take a deep dive into Oracle AQ architecture and components.

Oracle AQ Architecture Overview

Now let’s see a high-level Oracle AQ architectural diagram.

Here, the source application enqueues the messages, which Oracle AQ reads. As subscribers are registered the messages are dequeued into the target application

In response to the dequeued messages, the target application may or may not send the response/acknowledgment back to the source application. In the event of response messages, the target application sends the response to Oracle AQ by enqueuing the message in AQ and the source application dequeues the message to understand the response at the target system. 

Note: Affected queues need to be to register and subscribe to be able to send and receive the messages. Under the AQ Components topic, we will see how queues can be registered and subscribed. 

Now, let’s take one use case of Oracle AQ to have a better understanding and how it works.

Now we will see, what is inside Oracle AQ.

In Oracle AQ we primarily have queues, queue tables, message processing, and propagation. Here, exception queues are maintained to handle any failover cases. 

Also, you will notice that automated propagation can be set which allows the messages can be transmitted from one database to another queue in the same or different database. This is particularly, useful for distributed applications which are dealing with multiple systems. In the next chapter we will see what core AQ components are.

AQ Components

As seen in the above diagram (architectural overview), the primary components of Oracle AQ are:

• Queue: Logical container for the payloads, Multiple queues can be created based on the application requirements.
• Tables: Tables specifically designed for storing the payloads and metadata.
• Message: It is actual data transmitted through the queue; they typically have the information required for its consumption in the application.
• Subscriber: Subscribers need to be set to consume the messages available in the queue. It can be an Oracle trigger or a procedure.

Creating and Configuring Advanced Queues

Before creating queues, queue tables must be created. For the same, you may need to work with the local DBA. DBA may need to define queue tables and manage permissions. To create queue tables, execute the below sample SQL statement.

CREATE TABLE my_queue_table(

    queue_column1   datatype,

    queue_column2   datatype,

    ... );

CREATE TABLE my_queue (

    id     NUMBER,

   some_data   VARCHAR2(100)

);

Once, queue tables are created, proceed with the creation of queues. Execute below SQL block below.

Note: The queue itself is a logical container for messages of a specific type.

BEGIN
    DBMS_AQADM.CREATE_QUEUE (
        queue_name     => 'queue_name',
        queue_table    => 'my_queue_table',
        queue_type     => 'NORMAL_QUEUE'
    );
END;

Once queues are created, grants must be given to it, so that, the required user(s) can have access to it.

GRANT EXECUTE ON dbms_aqadm TO myuser;
GRANT EXECUTE ON dbms_aq TO myuser;
GRANT ENQUEUE, DEQUEUE ON my_queue TO myuser;

Enqueuing and Dequeuing Messages

Before enqueuing or dequeuing the message, one must register and subscribe to the queues. After grants, make sure the subscriber for the queue has been registered so that the message can be enqueued or dequeued. Refer to the SQL block below.

Select count(1) into w_subscr from dba_queue_subscription
Where queue_name = ‘my_queue’ and consumer_name = ‘udf_consumber’;
If (w_subscr <=0) then
Dbms_aqadm.add_subscriber(queue_name => ‘my_queue’
    consumer_name = >‘udf_consumber );
End If;
Select count(1) into w_subscr_registrations from dba_subscr_registrations
Where subscription_name = ‘my_queue: udf_consumber’;
If (w_subscr_registrations = 0) Then
Dbms_aq.register( sys.aq$_reg_info_list(
           sys.aq$_reg_info(
             name      => 'my_queue: udf_consumbe',
             namespace =>  dbms_aq.namespace_aq,
             callback  => 'plsql://calling_trigger_to_setup_func_or_proc',
             context   =>  NULL)       ),       1);  
End If;

Once queues are registered and subscribed, let's see how the message can be enqueued or dequeued.

DECLARE
    enqueue_options  DBMS_AQ.ENQUEUE_OPTIONS_T;
    message_properties  DBMS_AQ.MESSAGE_PROPERTIES_T;
    message_handle  RAW(16);
BEGIN
    DBMS_AQ.ENQUEUE (
        queue_name         => 'my_queue',
        enqueue_options    => enqueue_options,
        message_properties => message_properties,
        payload            => 'This is my first AQ message',
        msgid              => message_handle
    );
    COMMIT;
END;

This will enqueue the message “This is my first AQ message” in the queue 'my_queue' Now, let's see how messages are dequeued. Note the block.

'plsql://calling_trigger_to_setup_func_or_proc'

Should call the code below.

DECLARE
    dequeue_options  DBMS_AQ.DEQUEUE_OPTIONS_T;
    message_properties  DBMS_AQ.MESSAGE_PROPERTIES_T;
    message_handle  RAW(16);
    message_payload VARCHAR2(4000);
BEGIN
    DBMS_AQ.DEQUEUE (
        queue_name         => 'my_queue',
        dequeue_options    => dequeue_options,
        message_properties => message_properties,
        payload            => message_payload,
        msgid              => message_handle
    );
    DBMS_OUTPUT.PUT_LINE('Message dequeued: ' || message_payload);
    COMMIT;
END;

Note: the payload messages are queued and stored in my_queue_table and notice that COMMIT statement is important to move to the next message(s). 

Advanced Queue Features

Message Prioritization and Scheduling

Oracle Advanced Queuing (AQ) gives you control over the timing and way messages are handled inside a queue by offering a number of tools that facilitate scheduling and message prioritizing.

You can give messages in a queue varying degrees of urgency or priority by using message prioritizing. Prioritization utilizing message properties is supported by Oracle AQ.

Sample SQL Block to set message priority:

message_properties  DBMS_AQ.MESSAGE_PROPERTIES_T;
message_properties.priority := 1;  -- Set the priority of the message

Similarly, for the messages to be dequeue, we can set a priority/order by:

dequeue_options DBMS_AQ.DEQUEUE_OPTIONS_T;
dequeue_options.navigation := DBMS_AQ.FIRST_MESSAGE;

Message scheduling allows you to delay the delivery of messages until a specified time or to schedule recurring deliveries of messages. This is achieved using the enqueue_time attribute in the message properties.

enqueue_time TIMESTAMP := SYSTIMESTAMP - 1;

Message Grouping and Batching

Message grouping and batching are particularly useful when we want to optimize and improve performance. Group can be created at the time of queue creation. Messages belonging to one queue can be grouped to form a set that can only be consumed by one user at a time. This requires that the queue be created in a queue table that is enabled for transactional message grouping. This helps in categorizing messages based on business logic or processing requirements.

Every message in a group has a group or message ID and sequence number. To identify the message as belonging to which group or message. A set of messages that can only be viewed by one user at a time can be created by grouping messages from the same queue. To accomplish this, the queue must be established in a queue table with transactional message grouping enabled. 

To understand this, let's assume, you have multiple external data providers for similar data points. You can group them in a different queue table. This will help to track and trace the messages received from the different data providers and act accordingly.

Sending several messages at once in a single transaction or process is known as batching. This can increase throughput and lower the overhead related to performing numerous separate tasks.

• Batching can be implemented at the enqueue and dequeue levels.
• Using queue arrays, multiple messages can be processed in bulk to reduce network traffic.
• It can maintain transaction integrity which makes sure that, messages are successfully enqueued or dequeued.

Queue Monitoring and Performance Tuning

To guarantee effective operation and peak performance, Oracle queues, especially those with Oracle Advanced Queuing (AQ) — need to be monitored and performance-tuned in a few important ways. Here's a whole strategy:

• To make sure the queue is staying within reasonable bounds, periodically check its depth and size.
• To query queue statistics, including the number of messages (NUM_MESSAGES), the size of the queue as of right now (CURRENT_SIZE), and the maximum size of the queue (MAX_SIZE), use the DBA_QUEUE_TABLES and DBA_QUEUES views.
• Monitor data such as average message size (AVERAGE_MESSAGE_SIZE), processing times (ENQUEUE_TIME, DEQUEUE_TIME), and enqueue and dequeue rates (ENQUEUE_RATE, DEQUEUE_RATE).
• To lower the overhead of individual transactions, use batch processing (BULK ENQUEUE and BULK DEQUEUE).
• To collect these metrics, use Oracle Enterprise Manager or custom scripts that query the DBA_QUEUE_SCHEDULES and DBA_QUEUE_SUBSCRIBERS views.
• Adjust the transaction parameters (DEQUEUE_BATCH_SIZE, COMMIT_BATCH_SIZE) to strike a balance between commit frequency and transaction size.
• Track messages that fail to enqueue or dequeue due to issues by keeping a watch on the error queues (DBA_QUEUE_ERRORS).
• Use Oracle performance tools like AWR reports (DBMS_WORKLOAD_REPOSITORY) and ASH reports (DBMS_ACTIVE_SESSION_HISTORY) to analyze queue-related performance metrics.

Integration With Oracle Streams and Oracle Goldengate

Advanced queues can be integrated with both Oracle Streams and Oracle Goldengate to provide reliable and efficient data integration and replication processes within Oracle databases. Streams is a built-in feature of Oracle Database used for data replication and messaging. Whereas, with real-time data movement and synchronization across heterogeneous systems, Oracle Goldengate is a complete data integration and replication solution.

• Streams ensure reliable message delivery and consistency across distributed systems.
• It enables real-time propagation of messages and data changes across the enterprise.
• It supports various data formats and types, making it compatible with different integration scenarios.
• Oracle AQ can act as a source or target for Oracle Goldengate, enabling bidirectional data replication and synchronization.

Also, it supports integration across different platforms and databases beyond Oracle, enhancing enterprise data integration capabilities and making more suitable options for selecting Oracle advanced queue.

Use Case and Scenarios

So far, we have seen the in-depth of Oracle Advance Queue, finally ramping up the guild with a use case and affected scenario, while opting for Oracle AQ.

• Event-driven architecture for real-time data

Enqueuing messages are stored locally on specific events. Payloads are dequeued to process and affect downstream applications or processes.

• Asynchronous Processing for decoupled applications

Instruments (investment securities) are getting opened parallelly via data refresh from external data providers. The processing of one security is totally independent of another hence in the real business world there is almost no stoppage.

• Batch and Scheduling in a controlled manner

Enqueuing job requests into an AQ queue, where a dedicated job processing application dequeues and executes jobs according to defined schedules or priorities.

• Scalability and Load Balancing to handle parallel processing and increased load

Oracle AQ is effectively handling bulk and processing multiple payloads at a time.  

Best Practices and Troubleshooting

• It is recommended to use automatic segment-space management (ASSM) tablespaces for the AQ queue tables.
• The queue table indexes, and index-organized tables (IOT)s are automatically merged by AQ background processes. However, they must continue to be monitored and coalesced if needed.
• Oracle Real Application Clusters (RAC) can be used to provide high availability and scalability to AQ. The performance of AQ can be improved by allowing different queues to be managed by different RAC instances.
• Ensure that there are enough queue monitor processes running to perform the background tasks. The queue monitor must also be running for other crucial background activities.
• Ensure that statistics are being gathered so that the optimal query plans for retrieving messages are being chosen. By default, queue tables are locked out from the automatic gathering of statistics.
• Dequeue with a wait time should only be used with dedicated server processes. In a shared server environment, the shared server process is dedicated to the dequeue operation for the duration of the call, including the wait time. The presence of many such processes can cause severe performance and scalability problems and can result in deadlock.
• Batching multiple dequeue operations on multi-consumer queues into a single transaction, using NEXT as the navigation mode if not using message priorities, and using the REMOVE_NODATA dequeue mode if dequeuing in BROWSE mode followed by a REMOVE.

Conclusion

Oracle AQ provides a solid foundation for developing scalable, dependable, and event-driven applications in Oracle Database environments, making it ideal for a wide range of enterprise use cases that require efficient messaging and data integration capabilities.

AQ features like varying service quality, automatic message transformations, and propagations enable enterprises to create a robust and adaptable messaging platform. Database-integrated Advanced Queuing enables real-time flow of essential information, reduces management, and boosts productivity for your scalable, highly available organization.

If the enterprise already has an Oracle platform, it is highly recommended to use advanced queueing at the enterprise level to cater to various data points across different systems.

If the enterprise is doing a feasibility analysis to select the correct platform and environment, they can certainly consider, Oracle and Oracle Advance Queue, as it will give them a robust, scalable platform on which the organization can build an IT landscape.