What should be the Correct Setting for Parameter AQ_TM_PROCESSES in E-Business Suite Instance?

What should be the Correct Setting for Parameter AQ_TM_PROCESSES in E-Business Suite Instance?


Oracle Workflow Cartridge - Version 11.5.10.0 to 11.5.10.2 [Release 11.5.10]
Information in this document applies to any platform.
Checked for relevance on 03-MAR-2012


GOAL

That note explains the importance of parameter AQ_TM_PROCESSES, in an E-Business Suite instance, and then gives some tips to set it to the correct value, depending on the database version.

FIX

The AQ_TM_PROCESSES parameter determines the number of AQ background processes that will be started to perform Queue Monitoring. Queue Monitoring is responsible for removing the processed messages from the queues (after the retention time of the queue has been reached), and for changing the status of the delayed messages from "Waiting" to "Ready" (when the delay has been reached). As E-Business Suite operates many queues, of course, in an E-Business Suite instance, Queue Monitoring must be active. So it is important to properly set AQ_TM_PROCESSES, by following the instructions below:

1- The parameter AQ_TM_PROCESSES must never be set to 0, whatever the version of the database is, otherwise Queue Monitoring will not occur.

2- For pre-10g databases:

AQ_TM_PROCESSES has to be set in the database parameter file, and it must originally be set to 1.  That value allows to start 1 AQ background process for Queue Monitoring, and this is usually sufficient for most E-Business Suite instances.  However, it can be increased, if you experience some delay in the queue maintenance.

3- For 10g+ databases:

Starting from 10g, Queue Monitoring can "auto-tune".  That means Queue Monitoring does not need AQ_TM_PROCESSES to be defined, it is instead able to adapt to the number of AQ background processes to the system load.

As a consequence, the parameter AQ_TM_PROCESSES no longer needs to be set in the database parameter file, but you can still define it:

     -> If one sets the parameter, then the behavior is similar to a pre-10g database, i.e. a number of AQ background processes will be started, according to the value of the parameter.

     -> If one  does not set the parameter, then the Queue Monitoring will "auto-tune", i.e. it will start the AQ background processes that are needed to handle the load, and will adapt the number of processes, as the load changes.

So, ideally, in 10g+ database, you should no longer set AQ_TM_PROCESSES in the database parameter file, thus enabling the "auto-tune" feature ...

However, when AQ_TM_PROCESSES is not set in the database parameter file, and you query the parameter value from table V$PARAMETER, then the value appears as being 0.  Some commonly used data collection scripts (wfver.sql for instance), select the parameter value from V$PARAMETER. The output could be confusing, leading to think that AQ_TM_PROCESSES is actually set to 0.

Note : in 11.2.0.3 database, when "auto-tune" feature is enabled (i.e. aq_tm_processes is not defined in database parameter file), the value of aq_tm_processes will be shown as 1, so confusion is no longer possible, and one can run with "auto-tune" feature enabled.

So, the recommendation, for 10g+ databases, is still to set AQ_TM_PROCESSES in the database parameter file. And again, the original value should be 1, but it can be increased when some delay is noted in the queue maintenance.

Note

4- In case it needs to increase the value of AQ_TM_PROCESSES, it must be noted that, in 9.2 and 10g+ databases, the parameter AQ_TM_PROCESSES must not be set to 10, as setting it to 10 disables some other features of the Queue Monitoring.