These topics discuss performance and scalability issues relating to Transactional Event Queues (TEQ) and Advanced Queuing (AQ).
Transactional Event Queues
A transactional event queue (TEQ) increases enqueue-dequeue throughput, especially across Oracle Real Application Clusters (Oracle RAC) instances, because messages from different enqueue sessions are allowed to be dequeued in parallel. Each event stream of the queue is ordered based on enqueue time within a session and ordering across event streams is best-effort. TEQs automatically manage table partitions so that enqueuers and dequeuers do not contend among themselves. In addition, TEQs use an in-memory message cache to optimize performance and reduce the disk and CPU overhead of enqueues and dequeues.
The advantages and tradeoffs of TEQs include the following:
TEQs provide scalability of a single queue on Oracle RAC, especially in the case where each subscriber has multiple dequeuers on each instance.
Oracle Real Application Clusters (Oracle RAC)s trades off increased memory usage to obtain performance.
This section contains the following topics:
Transactional Event Queues and the Message Cache
TEQs introduce a special purpose message cache which lets you trade off SGA usage for increased throughput, reduced latency, and increased concurrency. When combined with partitioning, the message cache reduces the need for some queries, DML operations, and indexes. The message cache is most effective when all dequeuers keep up with enqueuers and when the message cache is big enough to store messages (including payloads) for each TEQ's enqueuers and dequeuers. The message cache uses the Streams pool. If TEQs share the Streams pool on the same instance as Streams replication functionality, you can use
DBMS_AQADM procedures such as
SET_MAX_STREAMS_POOL to fine tune the allocation of Streams Pool memory.
Oracle Database PL/SQL Packages and Types Reference for information
Transactional Event Queues and Enqueuing / Dequeuing Messages
To improve throughput and reduce overhead and latency, enqueues and dequeues are optimized to use the message cache, the rules engine, and background processing when possible. For example,
TEQs take advantage of new rules engine improvements
a message that has its payload in the message cache does not have to be re-read from disk during a dequeue
dequeue by correlation id or other JMS properties can often be evaluated without accessing the disk
partition operations on TEQs implements efficient bulk processing.
Transactional Event Queues and Native JMS Support
TEQs have native support for:
TEQs support both persistent and nonpersistent messages. Nonpersistent messages are stored in memory inside the message cache and are not stored on disk. As a result, nonpersistent messages are lost upon instance crash or shutdown.
TEQs natively support two kinds of subscribers to meet the JMS requirements:
Non-durable subscribers: These subscribers receive messages on their chosen topic, only if the messages are published while the subscriber is active. This subscription is not sharable among different sessions.
Durable subscribers: These subscribers receive all the messages published on a topic, including those that are published while the subscriber is inactive. Multiple database sessions can share the same subscription.
TEQs do not use ADTs to store the JMS payload. The JMS message is stored in scalar columns of the database. JMS message types such as
OBJECT store the JMS payload in scalar
CLOB/BLOB columns in the queue table depending on payload size and type. The JMS message properties are stored in a
CLOB (SecureFile) column in the queue table with attribute access functions defined for the user defined properties. The payload and user properties are pickled into
VARCHAR2 or Secure File columns instead of being stored as an ADT. JMS Header properties and JMS provider information are stored in their own scalar columns.
TEQs support integer priority values ranging between 0 (lowest priority) and 9 (highest priority), with the default being priority 4, as defined by the JMS standard.
Transactional Event Queues and Partitioning
TEQs automatically manage the underlying partitioned tables used for the queue table. Such partition management may occur in the foreground or the background. Each event stream provides session-level ordering of enqueued messages. Each enqueuing session is assigned an event stream. Each event stream is composed of a series of event stream partitions. Each event stream partition maps to a single partition. Messages are automatically assigned to a table partition upon enqueue.
New partitions are automatically created as needed, as when the queue table needs to grow when dequeuers do not keep up with enqueuers. Partitions are truncated and reused when all messages in the partition are dequeued and no longer needed. The message cache automatically loads messages from partitions into memory as required by dequeuers. Global indexes should not be created on the partitioned table underlying a TEQ. Local indexes are not typically recommended on the partitioned table either. If such indexes are desired and result in performance degradation, then AQ queues should be considered.
Transactional Event Queues and Oracle Real Application Clusters (Oracle RAC)
TEQs automatically provides enqueue session ordering while avoiding cross-instance communication when possible. Sometimes cross instance communication is required. For example, if a TEQ has a single enqueuing session on one Oracle RAC instance and a single dequeuing session on another instance, then TEQs will forward messages between the Oracle RAC instances. The forwarding of messages is non-atomic to the enqueuing transaction to improve performance. Dequeuers may get an ORA-25228 if they are connected to an instance that has no messages in its event streams.
In most cases, consider having multiple dequeuers for each subscriber or single consumer queue on each Oracle RAC instance to improve throughput and reduce cross-instance overhead. An exception to this guideline is when you are using dequeue selectors that specify a single message. If you want to dequeue a message from a TEQ by its message identifier in an Oracle RAC database, then you have to connect to the instance that is assigned dequeue ownership for the event stream containing the message. Otherwise, the message will not be available for dequeue to the dequeue session. If all dequeues are performed at a single instance, then messages will be automatically forwarded to this instance. Hence, for a single-consumer TEQ that extensively dequeues by message ID, consider having all dequeue sessions for the TEQ connect to a single instance. Similarly, for a multiconsumer TEQ that extensively dequeues by message ID, consider having all dequeue sessions for each subscriber connect to a single instance. Services can be used to simplify connecting dequeue sessions to a particular instance.
Transactional Event Queues and Message Retention
Starting from Oracle Database Release 20c, message retention is supported by TEQ . AQ queue already has this feature.
Message retention is the time for which a message is retained in the TEQ after being enqueued or dequeued as desired. The default is 0, which means that the message will be removed as soon as possible after it is dequeued by all of its subscribers. This helps users to retain the messages in the queue even after they are processed.
Applications can specify retention time while creating a TEQ. Applications can change the retention time and its type as needed after creation of the TEQ.
TEQ supports only dequeue time based retention. A event stream partition stores a set of messages. The event stream partition will be removed from the queue when the highest dequeue time for any message-subscriber pair in that event stream partition plus retention time is over. This scheme will ensure consumption of messages before retention comes in play. This is also the only retention policy available in AQ queues.
Transactional Event Queues and Seekable Subscribers
A seek operation for a subscriber can be for all event streams (queue level seek) or a set of specific event streams of choice (event stream level seek).
All the dequeue calls after a seek operation would dequeue messages from the seek point onwards. All the messages below seek point will never be dequeued or browsed by the subscriber unless the subscriber seeks back again.
A subscriber can perform seek in all event streams or a set of event streams of choice in the queue. The choice of the message to seek to can be explicitly specified in the seek operation or can be deduced from the inputs of seek operation.
Following are the different types of seek option inputs.
Seek to end – With this seek option, the subscriber is not interested in existing messages. The subscriber will be able to dequeue only newly enqueued messages after the seek operation. This is the default behavior when a new subscriber is created.
Seek to start - With this seek option, the subscriber is interested in existing messages including "retained". The subscriber will also be able to dequeue newly enqueued messages after the seek operation.
Seek to a specific time - With this seek option, the subscriber is interested in existing messages including "retained" with enqueue time higher than input time. Seek stops if start or end is reached.
Seek to a specific message – With this seek option, the subscriber is interested in existing messages including "retained" from the input message onwards. The input in this case is a specific message id so this seek always a event stream level seek. A separate unique message id per event stream is specified in the input for all the event streams on which seek needs to be performed.
A seek action can break message ordering as it results in out of order dequeues. If the new seek point is a message which is not the first message of an enqueue transaction or an enqueue session and messages from new seek point onwards are dequeued, then the application can get only some messages of the enqueue session or enqueue transaction as remaining messages were enqueued before the new seek point.
It is application's responsibility to either choose correct seek point or be tolerant to such behavior.
Transactional Event Queues Restrictions
The following Oracle Database features are not currently supported for TEQs:
Anonymous posting for subscriber notification and OCI callback notification are not supported. PL/SQL callback notification is supported.
Oracle extensions for JMS such as JMS propagation and remote subscribers
Multiple queues per queue table. TEQs are created via the
Ordering other than message priority followed by enqueue time (as specified in the JMS standard)
The JDBC thick (OCI) driver.
Propagation between TEQ and AQ queues
Transactional Event Queues Tuning
TEQs perform best under the following conditions:
Dequeuers for each subscriber are located on each instance
Subscribers keep up with the enqueuers. Consider having multiple dequeuers for each subscriber on each Oracle RAC instance
The message cache is most effective when dequeuers keep up with enqueuers and where the cache is big enough to store messages (including payloads) for each TEQ's enqueuers and dequeuers. When using TEQs, Oracle requires that you do one of the following:
This parameter controls the size of shared memory available to the Oracle Database for the TEQ message cache. If unspecified, up to 10% of the shared pool size may be allocated for the Streams pool.
Oracle's Automatic Shared Memory Management feature manages the size of the Streams pool when the
SGA_TARGETinitialization parameter is set to a nonzero value. If the
STREAMS_POOL_SIZEinitialization parameter also is set to a nonzero value, then Automatic Shared Memory Management uses this value as a minimum for the Streams pool.
STREAMS_POOL_SIZEinitialization parameter is set to a nonzero value, and the
SGA_TARGETparameter is set to
0(zero), then the Streams pool size is the value specified by the
STREAMS_POOL_SIZEparameter, in bytes.
If both the
SGA_TARGETinitialization parameters are set to
0(zero), then, by default, the first use of the Streams pool in a database transfers an amount of memory equal to 10% of the shared pool from the buffer cache to the Streams pool.
DBMS_AQADM.set_max_streams_pool( )in Oracle Database PL/SQL Packages and Types Reference for a finer grained control over
STREAMS_POOLsharing with Streams processing.
Turning on SGA autotuning
Oracle will automatically allocate the appropriate amount of memory from the SGA for the Streams pool, based on Streams pool usage as well as usage of other components that use the SGA. Examples of such other components are buffer cache and library cache. If
STREAMS_POOL_SIZEis specified, it is used as the lower bound.
Manually tuning TEQ queues
TEQs can be tuned by allocating
STREAMS_POOLmemory for the message cache. The view
GV$AQ_MESSAGE_CACHE_ADVICEprovides advice on how much
STREAMS_POOLshould be allocated for TEQs based on a snapshot of the current messaging load. During periods of high load, select the columns
ESTD_SIZE_TYPEis one of three values:
MAXIMUM. Find the maximum value of
SIZE_FOR_ESTIMATEacross Oracle RAC instances for each of the
ESTD_SIZE_TYPEvalues. It is highly recommended that
STREAMS_POOLbe set at least to the
MINIMUMrecommendation to provide any message cache performance gains. There is little additional performance gains to setting
STREAMS_POOLgreater than the
MAXIMUMrecommendation value. Setting
PREFERREDrecommendation tries to provide a reasonable space-performance tradeoff. If the
MAXIMUMsize recommendation is much greater than the
PREFERREDrecommendation, then check that the TEQ has no orphaned subscribers, or whether more dequeuers should be added to the instance, so that dequeuers can keep up with the enqueue load.
STREAMS_POOLtuning should be done over multiple periods of high load and whenever messaging load characteristics change.
User Event Streaming
An application can decide the way messages are event streamed in the TEQ. In such cases, the application explicitly specifies to enqueue a message in a specific event stream.
For example, assume the application has four types of messages with different keys named red, green, blue, and pink. Each enqueue session can enqueue any of those messages in a transaction. Event Stream A is set to store the red and blue messages. Event Stream B is set to store the green and pink messages. Also, each event stream is set to have only one active dequeue session for a single-consumer queue or JMS Queue. Similarly, each event stream is set to have only one dequeue session per subscriber for a multi-consumer queue or JMS Topic. That dequeue session will stick to that event stream for the dequeuer session’s lifetime.
In the following examples, enqueue transactions are performing enqueues in parallel.
Description of the illustration adque_usershard1.png
Applications can add new event streams at run time. Applications can also add new types of messages at run time by adding new keys. For example, two new types are introduced with keys orange and purple, and a third Event Stream C is added. Event Stream B is set to store the orange messages. Event Stream C is set to store the purple messages.
In an Oracle RAC database, a event stream is always owned by an instance. Initially, the event stream is owned by the instance where the first message is enqueued in that event stream. The owner instance of the event stream may change when database instances are shut down.
With user event streaming, a user can attempt to enqueue messages in an event stream which is not owned by the instance in which the session is running. In such cases, a cross instance enqueue is triggered. To support cross instance enqueues, the enqueue requests received at other instances are forwarded to the
OWNER INSTANCE of the event stream over the RAC interconnect. The
REMOTE_LISTENER parameter in listener.ora must also be set to enable forwarding of cross instance enqueue requests to the correct instance. Internally, TEQ queues on an Oracle RAC database may use database links between instances. Definer's rights PL/SQL packages that perform cross instance enqueues in TEQ queues on an Oracle RAC database must grant
INHERIT REMOTE PRIVILEGES to users of the package.
Limitations of User Event Streaming
User event streaming has the following limitations:
Cross instance enqueues are not enabled for PL/SQL enqueue calls.
Cross instance enqueues are not enabled for array enqueues.
Cross instance enqueues can be done through Java and OCI clients.
DBMS_AQADM in Oracle Database PL/SQL Packages and Types Reference for more information.
This section includes the following topics:
Persistent Messaging Basic Tuning Tips
Oracle Database Advanced Queuing table layout is similar to a layout with ordinary database tables and indexes.
Oracle Database Performance Tuning Guide for tuning recommendations
Streams pool size should be at least 20 MB for optimal multi-consumer dequeue performance in a non-Oracle RAC database.
Persistent queuing dequeue operations use the streams pool to optimize performance, especially under concurrency situations. This is, however, not a requirement and the code automatically switches to a less optimal code path.
TEQs introduces a message cache for optimal performance of high throughput messaging systems. Ideally the Streams pool size should be large enough to cache the expected backlog of messages in TEQs.
Using Storage Parameters
Storage parameters can be specified when creating a queue table using the
Storage parameters are inherited by other IOTs and tables created with the queue table. The tablespace of the queue table should have sufficient space to accommodate data from all the objects associated with the queue table. With retention specified, the history table and, also the queue table can grow to be quite big.
Oracle recommends you use automatic segment-space management (ASSM). Otherwise
initrans, freelists and freelist groups must be tuned for AQ performance under high concurrency.
PCTFREE will reduce the number of messages in a queue table/IOT block. This will reduce block level contention when there is concurrency.
Storage parameters specified at queue table creation are shared by the queue table, IOTs and indexes. These may be individually altered by an online redefinition using
Because Oracle Database Advanced Queuing is very I/O intensive, you will usually need to tune I/O to remove any bottlenecks.
"I/O Configuration and Design" in Oracle Database Performance Tuning Guide
Running Enqueue and Dequeue Processes Concurrently in a Single Queue Table
Some environments must process messages in a constant flow, requiring that enqueue and dequeue processes run concurrently. If the message delivery system has only one queue table and one queue, then all processes must work on the same segment area at the same time. This precludes reasonable performance levels when delivering a high number of messages.
The best number for concurrent processes depends on available system resources. For example, on a four-CPU system, it is reasonable to start with two concurrent enqueue and two concurrent dequeue processes. If the system cannot deliver the wanted number of messages, then use several subscribers for load balancing rather than increasing the number of processes.
Tune the enqueue and dequeue rates on the queue so that in the common case the queue size remains small and bounded. A queue that grows and shrinks considerably will have indexes and IOTs that are out of balance, which will affect performance.
With multi-consumer queues, using several subscribers for load balancing rather than increasing the number of processes will reduce contention. Multiple queue tables may be used garnering horizontal scalability.
For information about tuning TEQs refer to Transactional Event Queues Tuning.
Running Enqueue and Dequeue Processes Serially in a Single Queue Table
When enqueue and dequeue processes are running serially, contention on the same data segment is lower than in the case of concurrent processes. The total time taken to deliver messages by the system, however, is longer than when they run concurrently.
Increasing the number of processes helps both enqueuing and dequeuing. The message throughput rate may be higher for enqueuers than for dequeuers when the number of processes is increased, especially with single consumer queues. Dequeue processes on multi-consumer queues scale much better.
Creating Indexes on a Queue Table
Creating an index on a queue table is useful if you meet these conditions.
Dequeue using correlation ID
An index created on the column
corr_idof the underlying queue table
Dequeue using a condition
This is like adding the condition to the where-clause for the
SELECTon the underlying queue table. An index on
QueueTableNameexpedites performance on this
Other Tips for Queues
These are some other persistent messaging basic tuning tips.
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 automatic gathering of statistics. The recommended use is to gather statistics with a representative queue message load and lock them.
The queue table indexes and IOTs are automatically coalesced by AQ background processes. However, they must continue to be monitored and coalesced if needed. With automatic space segment management (ASSM), an online shrink operation may be used for the same purpose. A well balanced index reduces queue monitor CPU consumption, and ensures optimal enqueue-dequeue performance.
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 activity. Multiple
qmnprocesses share the load; make sure that there are enough of them. These are auto-tuned, but can be forced to a minimum number, if needed.
It is recommended that dequeue with a wait time is only 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 deadlocking the shared server processes.
Long running dequeue transactions worsen dequeue contention on the queue, and must be avoided.
Batching multiple dequeue operations on multi-consumer queues into a single transaction gives best throughput.
NEXTas navigation mode, if not using message priorities. This offers the same semantics but improved performance.
REMOVE_NODATAdequeue mode if dequeuing in
BROWSEmode followed by a
Propagation Tuning Tips
Propagation can be considered a special kind of dequeue operation with an additional
INSERT at the remote (or local) queue table. Propagation from a single schedule is not parallelized across multiple job queue processes. Rather, they are load balanced.Propagation can be considered a special kind of dequeue operation with an additional
INSERT at the remote (or local) queue table. Propagation from a single schedule is not parallelized across multiple job queue processes. Rather, they are load balanced.
For better scalability, configure the number of propagation schedules according to the available system resources (CPUs).
Propagation rates from transactional and nontransactional (default) queue tables vary to some extent because Oracle Database Advanced Queuing determines the batching size for nontransactional queues, whereas for transactional queues, batch size is mainly determined by the user application.
Optimized propagation happens in batches. If the remote queue is in a different database, then Oracle Database Advanced Queuing uses a sequencing algorithm to avoid the need for a two-phase commit. When a message must be sent to multiple queues in the same destination, it is sent multiple times. If the message must be sent to multiple consumers in the same queue at the destination, then it is sent only once.
Buffered Messaging Tuning
Buffered messaging operations in a Oracle Real Application Clusters environment will be fastest on the
OWNER_INSTANCE of the queue.
Persistent Messaging Performance Overview for Queues
When persistent messages are enqueued, they are stored in database tables. The performance characteristics of queue operations on persistent messages are similar to underlying database operations.
The code path of an enqueue operation is comparable to
INSERT into a multicolumn queue table with three index-organized tables. The code path of a dequeue operation is comparable to a
SELECT operation on the multi-column table and a
DELETE operation on the dequeue index-organized table. In many scenarios, for example when Oracle RAC is not used and there is adequate streams pool memory, the dequeue operation is optimized and is comparable to a
SELECT operation on a multi-column table.
Performance is not affected by the number of queues in a table.
Queues and Oracle Real Application Clusters
Oracle Real Application Clusters (Oracle RAC) can be used to ensure highly available access to queue data.
The entry and exit points of a queue, commonly called its tail and head respectively, can be extreme hot spots. Because Oracle RAC may not scale well in the presence of hot spots, limit usual access to a queue from one instance only. If an instance failure occurs, then messages managed by the failed instance can be processed immediately by one of the surviving instances. If AQ queues are experiencing hot spots, then consider using TEQs instead.
You can associate Oracle RAC instance affinities with 8.1-compatible queue tables. If you are using
q2 in different instances, then you can use
CREATE_QUEUE_TABLE on the queue table and set
primary_instance to the appropriate
Oracle Database Advanced Queuing in a Shared Server Environment
Queue operation scalability is similar to the underlying database operation scalability.
If a dequeue operation with wait option is applied, then it does not return until it is successful or the wait period has expired. 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 deadlocking the shared server processes. For this reason, Oracle recommends that dequeue requests with wait option be applied using dedicated server processes. This restriction is not enforced.
"DEQUEUE_OPTIONS_T Type" in Oracle Database PL/SQL Packages and Types Reference for more information on the wait option
Oracle provides these views to monitor system performance and troubleshooting.
These views are integrated with the Automatic Workload Repository (AWR). Users can generate a report based on two AWR snapshots to compute enqueue rate, dequeue rate, and other statistics per queue/subscriber.