Tuesday, September 4, 2012

aq_tm_processes significance for Oracle Streams

From 10.1 and up it is no longer necessary to set AQ_TM_PROCESSES when Oracle Streams AQ or Streams is used, but if you a value is specified, then that value is taken into account but the number of processes can still be auto-tuned.

If  AQ_TM_PROCESSES is explicitly specified then the process(es) started will only maintain persistent messages. For example if aq_tm_processes=1 then at least one queue monitor slave process will be dedicated to maintaining persistent messages. Other process can still be automatically started to maintain buffered messages. 

Up to and including version 11.1 if you explicitly set aq_tm_processes = 10 then there will be no processes available to maintain buffered messages. This should be borne in mind in environments which use Streams replication and from 10.2 onwards user enqueued buffered messages.

In addition you should never disable the Queue Monitor processes by setting aq_tm_processes=0 on a permanent basis. .

To check whether auto-tuning is enabled or aq_tm_processes=0 do the following:

connect / as sysdba

set serveroutput on

mycheck number;
select 1 into mycheck from v$parameter where name = 'aq_tm_processes' and value = '0' and (ismodified != 'FALSE' OR isdefault = 'FALSE');
if mycheck = 1 then
dbms_output.put_line('The parameter ''aq_tm_processes'' is explicitly set to 0!');
end if;
exception when no_data_found then
dbms_output.put_line('The parameter ''aq_tm_processes'' is not explicitly set to 0.');

If it is, then it is recommended to unset the parameter. However, this requires bouncing the database. In the meantime, if the database cannot be immediately bounced, the recommended value to set it to is '1', and this can be done dynamically:

References : Oracle Support Note: 305662.1