Blog dedicated to Oracle Applications (E-Business Suite) Technology; covers Apps Architecture, Administration and third party bolt-ons to Apps

Thursday, July 30, 2009

What is a queue in Oracle ?

As per, a queue is a first-in first-out data structure. Objects are added (enqueued) to the tail of the queue and taken off the head (dequeued). This is same as a real life queue for tickets or food.

Queues are used to process multiple demands for a resource such as a printer, processor or communications channel (deferred execution of work). Queues also serve as foundation technology for workflow applications.

Queue Tables can be created on an Oracle database with Oracle's Advanced Queueing feature.

Oracle Advanced Queueing (AQ) is the Oracle database's queue management feature. AQ provides a message queuing infrastructure as integral part of the Oracle server engine. It provides an API for enqueing messages to database queues. These messages can later be dequeued for asynchronous processing. Oracle AQ also provides functionality to preserve, track, document, correlate, and query messages in queues.

Oracle AQ was first introduced in Oracle 8.
As of Oracle release 9.2, AQ is bundled with Standard Edition and Enterprise Edition at no extra cost.
As of Oracle release 10.1, AQ is integrated into Oracle Streams, and is called "Oracle Streams AQ".

Metalink Note 316889.1 about 10gr2 manual upgrade recommends that streams_pool_size set to 200MB as an ideal setting.

You can check the value and current size of streams_pool_size with this query:

select component, current_size/1024/1024 "CURRENT_SIZE",
min_size/1024/1024 "MIN_SIZE",
user_specified_size/1024/1024 "USER_SPECIFIED_SIZE",
last_oper_type "TYPE"
from v$sga_dynamic_components;

In Oracle Applications world, the Workflow Notification Mailer is the heaviest user of Oracle streams. One of the recommendation for all the workflow queue's health would be to set the streams_pool_size parameter to 300M.

No comments: