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:
See Getting to Know Oracle8 and the Oracle8 Enterprise Edition for more information about the features and options available with Oracle8 Enterprise Edition.
For more information about Oracle AQ, see Oracle8 Application Developer's Guide.
Communication between programs can be classified into one of two types:
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.
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 the integrity or 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 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 a PL/SQL interface. 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:
Oracle AQ queues are implemented in database tables, so that 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.
Oracle AQ has five basic entities: messages, queues, queue tables, agents, 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 transfered 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.
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 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" on page 16-5) and allows you to collect interval statistics (see "Queuing Statistics" on page 16-8).
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_PROCESS specifies creation of one or more queue monitor processes at instance startup.
This section describes the major features of Oracle Advanced Queuing.
You can use object types to structure and manage the payload. (The RAW datatype can be used for unstructured payloads.)
Oracle AQ stores messages in tables. All standard database features such as recovery, restart, and Oracle Enterprise Manager are supported.
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.
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" on page 16-8).
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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 (see "Windows of Execution" on page 16-5) to keep interval statistics, which include:
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.
You can assign an identifier to each message. This identifier can be used to retrieve specific messages.
Oracle Enterprise Manager provides a graphical interface (GUI) for some of the queue administration functions, including starting and stopping a queue, scheduling and unscheduling propagation, and viewing queue properties as part of the schema manager.
For detailed information about Oracle AQ, see the Oracle8 Application Developer's Guide.