Sharded queues 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 sharded queue's enqueuers and dequeuers. The message cache uses the Streams pool. If sharded queues share the Streams pool on the same instance as Streams replication functionality, you can use DBMS_AQADM procedures such as SET_MIN_STREAMS_POOL and SET_MAX_STREAMS_POOL to fine tune the allocation of Streams Pool memory.

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,

• sharded queues 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 sharded queues implements efficient bulk processing.

Sharded queues have native support for:

• Non-Durable Subscribers

• JMS payloads

• Priorities

Sharded queues 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.

Sharded queues 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.

Sharded queues 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 TEXT, BYTES, MAP, STREAM and OBJECT store the JMS payload in scalar TEXT/RAW or 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 RAW, 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.

Sharded queues 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.

Sharded queues automatically manage the underlying partitioned tables used for the queue table. Such partition management may occur in the foreground or the background. Each shard provides session-level ordering of enqueued messages. Each enqueuing session is assigned a shard. Each shard is composed of a series of subshards. Each subshard 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 sharded queue. Local indexes are not typically recommended on the partitioned table either. If such indexes are desired and result in performance degradation, then non-sharded queues should be considered.

Sharded queues automatically provides enqueue session ordering while avoiding cross-instance communication when possible. Sometimes cross instance communication is required. For example, if a sharded queue has a single enqueuing session on one Oracle RAC instance and a single dequeuing session on another instance, then sharded queues 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 shards.

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 sharded queue by its message identifier in an Oracle RAC database, then you have to connect to the instance that is assigned dequeue ownership for the shard 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 sharded single-consumer queue that extensively dequeues by message ID, consider having all dequeue sessions for the sharded queue connect to a single instance. Similarly, for a sharded multiconsumer queue 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.

The following Oracle Database features are not currently supported for sharded queues:

• Message retention

• Transaction grouping

• Anonymous posting for subscriber notification and OCI callback notification are not supported. PL/SQL callback notification is supported.

• Messaging Gateway

• Oracle extensions for JMS such as JMS propagation and remote subscribers

• Multiple queues per queue table. Sharded queues are created via the CREATE_SHARDED_QUEUE interface.

• Ordering other than message priority followed by enqueue time (as specified in the JMS standard)

• The JDBC thick (OCI) driver.

• Propagation between sharded and non-sharded queues

• Message transformations

Sharded queues 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 sharded queue's enqueuers and dequeuers. When using sharded queues, Oracle requires that you do one of the following:

• Setting parameter STREAMS_POOL_SIZE

  This parameter controls the size of shared memory available to the Oracle Database for the sharded queue 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_TARGET initialization parameter is set to a nonzero value. If the STREAMS_POOL_SIZE initialization parameter also is set to a nonzero value, then Automatic Shared Memory Management uses this value as a minimum for the Streams pool.

  If the STREAMS_POOL_SIZE initialization parameter is set to a nonzero value, and the SGA_TARGET parameter is set to 0 (zero), then the Streams pool size is the value specified by the STREAMS_POOL_SIZE parameter, in bytes.

  If both the STREAMS_POOL_SIZE and the SGA_TARGET initialization 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.

  See Also:

  • DBMS_AQADM.set_min_streams_pool() and DBMS_AQADM.set_max_streams_pool( ) in Oracle Database PL/SQL Packages and Types Reference for a finer grained control over STREAMS_POOL sharing 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_SIZE is specified, it is used as the lower bound.

• Manually tuning sharded queues

  Sharded queues can be tuned by allocating STREAMS_POOL memory for the message cache. The view GV$AQ_MESSAGE_CACHE_ADVICE provides advice on how much STREAMS_POOL should be allocated for sharded queues based on a snapshot of the current messaging load. During periods of high load, select the columns INST_ID, SIZE_FOR_ESTIMATE, and ESTD_SIZE_TYPE. ESTD_SIZE_TYPE is one of three values: MINIMUM, PREFERRED, or MAXIMUM. Find the maximum value of SIZE_FOR_ESTIMATE across Oracle RAC instances for each of the ESTD_SIZE_TYPE values. It is highly recommended that STREAMS_POOL be set at least to the MINIMUM recommendation to provide any message cache performance gains. There is little additional performance gains to setting STREAMS_POOL greater than the MAXIMUM recommendation value. Setting STREAMS_POOL to the PREFERRED recommendation tries to provide a reasonable space-performance tradeoff. If the MAXIMUM size recommendation is much greater than the PREFERRED recommendation, then check that the sharded queue 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_POOL tuning should be done over multiple periods of high load and whenever messaging load characteristics change.