6 Oracle Streams AQ Performance and Scalability

This chapter discusses performance and scalability issues relating to Oracle Streams Advanced Queuing (AQ).

This chapter contains the following topics:

• Performance Overview

• Basic Tuning Tips

• Propagation Tuning Tips

Performance Overview

Queues are stored in database tables. The performance characteristics of queue operations are similar to underlying database operations. The code path of an enqueue operation is comparable to SELECT and INSERT into a multicolumn queue table with three index-organized tables. The code path of a dequeue operation is comparable to SELECT, DELETE, and UPDATE operations on similar tables.

Note: Performance is not affected by the number of queues in a table.

Oracle Streams AQ and Oracle Real Application Clusters

Oracle Real Application Clusters 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 Real Application Clusters 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.

Oracle Streams AQ 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.

See Also: "DEQUEUE_OPTIONS_T Type" in PL/SQL Packages and Types Reference for more information on the wait option

Basic Tuning Tips

Oracle Streams AQ table layout is similar to a layout with ordinary database tables and indexes.

See Also: Oracle Database Performance Tuning Guide for tuning recommendations

Using Storage Parameters

Storage parameters can be specified when creating a queue table using the storage_clause parameter. 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 as well as the queue table can grow to be quite big.

I/O Configuration

Because Oracle Streams AQ is very I/O intensive, you will usually need to tune I/O to remove any bottlenecks.

See Also: "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.

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 is higher for enqueuers than for dequeuers when the number of processes is increased. Usually, the dequeue operations throughput is much less than the enqueue operation (INSERT) throughput, because dequeue operations perform SELECT, DELETE, and UPDATE.

Creating Indexes on a Queue Table

Creating an index on a queue table is useful if you:

• Dequeue using correlation ID

  An index created on the column corr_id of the underlying queue table AQ$_QueueTableName expedites dequeues.

• Dequeue using a condition

  This is like adding the condition to the where-clause for the SELECT on the underlying queue table. An index on QueueTableName expedites performance on this SELECT statement.

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. 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 Streams AQ 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 Streams AQ 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.