Oracle8i Concepts
Release 8.1.5






Prev Next

Advanced Queuing

Many that are first shall be last; and the last shall be first.

Matthew 19:30: The Bible

This chapter describes the Oracle Advanced Queuing (Oracle AQ) feature. The chapter includes:

Introduction to Message Queuing

Communication between programs can be classified into one of two types:

Synchronous Communication

Synchronous communication is based on the request/reply paradigm--a program sends a request to another program and waits until the reply arrives.

This model of communication (also called online or connected) is suitable for programs that need to get the reply before they can proceed with their work. Traditional client/server architectures are based on this model.

The major drawback of the synchronous model of communication is that the programs must be available and running for the application to work. In the event of network or machine failure, programs cease to function.

Asynchronous Communication

In the disconnected or deferred model programs communicate asynchronously, placing requests in a queue and then proceeding with their work.

For example, an application might require entry of data or execution of an operation at a later time, after specific conditions are met. The recipient program retrieves the request from the queue and acts on it. This model is suitable for applications that can continue with their work after placing a request in the queue -- they are not blocked waiting for a reply.

For deferred execution to work correctly even in the presence of network, machine and application failures, the requests must be stored persistently, and processed exactly once. This can be achieved by combining persistent queuing with transaction protection.

Processing each client/server request exactly once is often important to preserve both the integrity and flow of a transaction. For example, if the request is an order for a number of shares of stock at a particular price, then execution of the request zero or two times is unacceptable even if a network or system failure occurs during transmission, receipt, or execution of the request.

Oracle Advanced Queuing

Oracle Advanced Queuing (Oracle AQ) integrates a message queuing system with the Oracle database. This allows you to store messages into queues for deferred retrieval and processing by the Oracle server.

Applications can access the queuing functionality through interfaces defined in PL/SQL, Java, C/C++ and Visual Basic. This provides a reliable and efficient queuing system without additional software like transaction processing (TP) monitors or Message Oriented Middleware.

Oracle AQ offers the following functionality:

Since Oracle AQ queues are implemented in database tables, all the operational benefits of high availability, scalability, and reliability are applicable to queue data. In addition, database development and management tools can be used with queues.

Queuing Entities

Oracle AQ has several basic entities: messages, queues, queue tables, agents, recipients, recipient and subscription lists, rules, rule-based subscribers and the queue monitor.


A message is the smallest unit of information being inserted into and retrieved from a queue. A message consists of control information and payload data. The control information represents message properties used by Oracle AQ to manage messages. The payload data is the information stored in the queue and is transparent to Oracle AQ. The datatype of the payload can be either RAW or an object type.

A message can reside in only one queue. A message is created by the enqueue call and consumed by the dequeue call. Enqueue and dequeue calls are part of the DBMS_AQ package.


A queue is the repository for messages. There are two types of queues: user (normal) queues and exception queues. The user queue is for normal message processing. All messages in a queue must have the same datatype. Messages are transferred to an exception queue if they cannot be retrieved and processed for some reason.

Queues can be created, altered, started, stopped, and dropped by using the DBMS_AQADM package.

Queue Tables

Queues are stored in queue tables. Each queue table is a database table and contains one or more queues. Each queue table contains a default exception queue.

Creating a queue table creates a database table with approximately 25 columns. These columns store Oracle AQ metadata and the user-defined payload.

A view and two indexes are created on the queue table. The view allows you to query the message data. The indexes are used to accelerate access to message data.


An agent is a queue user. There are two types of agents: producers who place messages in a queue (enqueuing) and consumers who retrieve messages (dequeuing). Any number of producers and consumers can access the queue at a given time.

An agent is identified by its name, address, and protocol. For an agent on a remote database, the only protocol currently supported is an Oracle database link, using an address of the form queue_name@dblink.


The recipient of a message may be specified by its name only, in which case the recipient must dequeue the message from the queue in which the message was enqueued. The recipient may be specified by name and an address with a protocol value of 0. The address should be the name of another queue in the same database or another Oracle8 database (identified by the database link) in which case the message is propagated to the specified queue and can be dequeued by a consumer with the specified name. If the recipient's name is NULL, the message is propagated to the specified queue in the address and can be dequeued by the subscribers of the queue specified in the address. If the protocol field is nonzero, the name and address field is not interpreted by the system and the message can be dequeued by special consumer (see third party support in the propagation section).

Recipient and Subscription Lists

A single message can be designed for consumption by multiple consumers. There are two ways to do this:

Different queues can have different subscribers, and the same recipient can be a subscriber to more than one queue. Further, specific messages in a queue can be directed toward specific recipients who may or may not be subscribers to the queue, thereby over-riding the subscriber list.


A rule is used to define one or more subscribers' interest in subscribing to messages that conform to that rule. The messages that meet this criterion are then delivered to the interested subscribers. Put another way: a rule filters for messages in a queue on a subject in which a subscriber is interested.

A rule is specified as a boolean expression (one that evaluates to true or false) using syntax similar to the WHERE clause of a SQL query. This boolean expression can include conditions on

Rule Based Subscriber

A rule-based subscriber is a subscriber that has rule associated with it in the default recipient list. A rule-based subscriber is sent a message that has no explicit recipients specified if the associated rule evaluates to TRUE for the message.

Queue Monitor

The queue monitor is an optional background process that monitors messages in the queue. It provides the mechanism for message expiration, retry, and delay (see "Windows of Execution") and allows you to collect interval statistics (see "Queuing Statistics").

The queue monitor process is different from most other Oracle background processes in that process failure does not cause the instance to fail.

The initialization parameter AQ_TM_PROCESSES specifies creation of one or more queue monitor processes at instance startup.

Features of Advanced Queuing

This section describes the major features of Oracle Advanced Queuing.

Structured Payload

You can use object types to structure and manage the payload. (The RAW datatype can be used for unstructured payloads.)

Integrated Database Level Operational Support

Oracle AQ stores messages in tables. All standard database features such as recovery, restart, and Oracle Enterprise Manager are supported.

SQL Access

Messages are stored as database records. You can use SQL to access the message properties, the message history, and the payload. All available SQL technology, such as indexes, can be used to optimize the access to messages.

The AQ_ADMINISTRATOR role provides access to information about queues.

Windows of Execution

You can specify that the consumption of a message has to occur in a specific time window. A message can be marked as available for processing only after a specified time elapses (a delay time) and as having to be consumed before a specified time limit expires.

The initialization parameter AQ_TM_PROCESS enables time monitoring on queue messages, which is used for messages that specify delay and expiration properties. Time monitoring must also be enabled if you want to collect interval statistics (see "Queuing Statistics").

If this parameter is set to 1, Oracle creates one queue monitor process (QMN0) as a background process to monitor the messages. If it is set to 2 through 10, Oracle creates that number of QMNn processes; if the parameter is not specified or is set to 0, then queue monitor processes are not created. The procedures in the DBMS_AQADM package for starting and stopping queue monitor operations are only valid if at least one queue monitor process was started with this parameter as part of instance startup.

Multiple Consumers per Message

A single message can be consumed by multiple consumers.


You have several options for selecting a message from a queue. You can select the first message or, once you have selected a message and established a consistent-read snapshot, you can retrieve the next message based on the current snapshot. You will acquire a new consistent-read snapshot every time you select the first message from the queue.

You can also retrieve a specific message using the message's correlation identifier.

Priority and Ordering of Messages

You have three options for specifying the order in which messages are consumed: A sort order that specifies which properties are used to order all message in a queue, a priority that can be assigned to each message, and a sequence deviation that allows you to position a message in relation to other messages.

If several consumers act on the same queue, a consumer will get the first message that is available for immediate consumption. A message that is in the process of being consumed by another consumer will be skipped.

Modes of Dequeue

A DEQUEUE request can either browse or remove a message. If a message is browsed it remains available for further processing. If a message is removed, it is not available any more for DEQUEUE requests. Depending on the queue properties a removed message may be retained in the queue table.

Waiting for the Arrival of Messages

A DEQUEUE could be issued against an empty queue. You can specify if and for how long the request is allowed to wait for the arrival of a message.

Retries with Delays

A message has to be consumed exactly once. If an attempt to dequeue a message fails and the transaction is rolled back, the message will be made available for reprocessing after a user-specified delay elapses. Reprocessing will be attempted up to the specified limit.

Exception Queues

A message may not be consumed within the given constraints, that is, within the window of execution or within the limits of the retries. If such a condition arises, the message will be moved to a user-specified exception queue.


ENQUEUE/DEQUEUE requests are normally part of a transaction that contains the requests. This provides the desired transactional behavior. However, you can specify that a request is a transaction by itself, making the result of that request immediately visible to other transactions.

Message Grouping

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 message grouping.

All messages belonging to a group have to be created in the same transaction and all messages created in one transaction belong to the same group. This feature allows you to segment complex messages into simple messages, for example, messages directed to a queue containing invoices could be constructed as a group of messages starting with the header message, followed by messages representing details, followed by the trailer message.


You can specify that messages be retained after consumption. This allows you to keep a history of relevant messages. The history can be used for tracking, data warehouse, and data mining operations.

Message History

Oracle AQ stores information about the history of each message. The information contains the ENQUEUE/DEQUEUE time and the identification of the transaction that executed each request.


If messages are retained they can be related to each other; for example, if a message m2 is produced as a result of the consumption of message m1, m1 is related to m2. This allows you to track sequences of related messages. These sequences represent "event journals" which are often constructed by applications. Oracle AQ is designed to let applications create event journals automatically.

Queue Level Access Control

With Oracle 8i, an owner of an 8.1 style queue can grant or revoke queue level privileges on the queue. DBAs can grant or revoke new AQ system level privileges to any database user. DBAs can also make any database user an AQ administrator.

Propagating Messages to Other Databases

Messages enqueued in one database can be propagated to queues on another database. The datatypes of the source and destination queues must match each other.

Message propagation enables applications to communicate with each other without being connected to the same database or the same queue. Propagation uses database links and Net8 between local or remote databases, both of which must have Oracle AQ enabled.

You can schedule (or unschedule) message propagation and specify the start time, the propagation window, and a date function for later propagation windows in periodic schedules. The data dictionary view DBA_QUEUE_SCHEDULES describes the current schedules for propagating messages.

The job queue background processes (SNPn) handle message propagation. To enable propagation, you must start at least one job queue process with the initialization parameter JOB_QUEUE_PROCESSES.

Propagation Statistics

Propagation statistics are available in the form of total/average number of messages/bytes propagated in a schedule. This information can be used to tune the performance of propagation of messages.

Non-Persistent Queues

AQ can deliver non-persistent messages asynchronously to subscribers. These messages can be event-driven and do not persist beyond the failure of the system (or instance). AQ supports persistent and non-persistent messages with a common API.

Publish/Subscribe Support

A combination of features are introduced to allow a publish/subscribe style of messaging between applications. These features include rule-based subscribers, message propagation, the listen feature and notification capabilities. Triggers can be used to publish system events and user events (see "Triggers on System Events and User Events").

Support for Oracle Parallel Server Environments

An application can specify the instance affinity for a queue-table. When AQ is used with parallel server and multiple instances, this information is used to partition the queue-tables between instances for queue-monitor scheduling. The queue-table is monitored by the queue-monitors of the instance specified by the user. If an instance affinity is not specified, the queue-tables will be arbitrarily partitioned among the available instances. There can be 'pinging' between the application accessing the queue-table and the queue-monitor monitoring it. Specifying the instance-affinity does not prevent the application from accessing the queue-table and its queues from other instances.

This feature prevents 'pinging' between queue monitors and AQ propagation jobs running in different instances. In Oracle8i release 8.1.5 an instance affinity (primary and secondary) can be specified for a queue table. When AQ is used with parallel server and multiple instances, this information is used to partition the queue-tables between instances for queue-monitor scheduling as well as for propagation. At any time, the queue table is affiliated to one instance. In the absence of an explicitly specified affinity, any available instance is made the owner of the queue table. If the owner of the queue table dies, the secondary instance or some available instance takes over the ownership for the queue table.

Queuing Statistics

Oracle AQ keeps statistics about the current state of the queuing system as well as time-interval statistics in the dynamic table GV$AQ.

Statistics about the current state of the queuing system include the numbers of ready, waiting, and expired messages.

One or more queue monitor processes must be started to keep interval statistics, which include:

Asynchronous Notification

OCI clients can use the new call OCISubscriptionRegister to register a callback for message notification. The client issues a registration call which specifies a subscription name and a callback. When messages for the subscription are received, the callback is invoked. The callback may then issue an explicit dequeue to retrieve the message.

Listen Capability (Wait for Messages on Multiple Queues)

The listen call is a blocking call that can be used to wait for messages on multiple queues. It can be used by a gateway application to monitor a set of queues. An application can also use it to wait for messages on a list of subscriptions. If the listen returns successfully, a dequeue must be used to retrieve the message.

Correlation Identifier

You can assign an identifier to each message. This identifier can be used to retrieve specific messages.


The import/export of queues constitutes the import/export of the underlying queue tables and related dictionary tables. Import and export of queues can only be done at queue table granularity.

When a queue table is exported, both the table definition information and the queue data are exported. When a queue table is imported, export action procedures maintain the queue dictionary. Because the queue table data is also exported, the user is responsible for maintaining application-level data integrity when queue table data are being transported.

For every queue table that supports multiple recipients, there is an index-organized table that contains important queue metadata. This metadata is essential to the operations of the queue, so you must export and import this index-organized table as well as the queue table for the queues in this table to work after import.

When the schema containing the queue table is exported, the index-organized table is also automatically exported. The behavior is similar at import time. Because the metadata table contains rowids of some rows in the queue table, import issues a note about the rowids being obsolete when importing the metadata table. This message can be ignored, as the queuing system automatically corrects the obsolete rowids as a part of the import process. However, if another problem is encountered while doing the import (such as running out of rollback segment space), the problem should be corrected and the import should be rerun.

Additional Information:

For detailed information about Oracle AQ, see Oracle8i Application Developer's Guide - Advanced Queuing.  


Copyright © 1999 Oracle Corporation.

All Rights Reserved.