First things first:

Thou shalt not explicitly set AQ_TM_PROCESSES=0 !

Unless, of course, you want to disable the Queue Manager Process (QMNC in Oracle 11.x). Which you may want to do during database upgrades to prevent Streams or Advanced Queueing from interfering with the upgrade process.

However, if you don’t reset this parameter afterwards, you might run into the following scenario the next time you do a Data Pump export or import:

At a customer site, regular export jobs that took only minutes to complete suddenly took 2 1/2 hours, seeming to be stuck at the very beginning. A quick diagnosis of that behaviour was achieved by using the “METRICS=Y” argument for expdp.

Running that job again interactively and checking v$session_wait, it showed the Data Pump Master process waiting on “Streams AQ: Enqueue Blocked On Low Memory“. This is usually a pointer to “streams_pool_size” being set too low (or having ASMM enabled resulting in a shrink operation on the Streams Pool at some point in time). There is also Bug 17365043 filed against Versions >= 11.2.0.3, which is unresolved yet, even in 12c.

BUT: it’s worth to first check the setting of AQ_TM_PROCESSES as shown in Oracle Support’s “Master Note for AQ Queue Monitor Process (QMON) (Doc ID 305662.1)”

connect / as sysdba

set serveroutput on

declare
 mycheck number;
 begin
  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.';
end;
/ 

In my customer’s scenario, resetting this parameter to its default and letting QMNC taking control of spawning processes resolved the Data Pump issue. Enlarging the streams pool did not help here, by the way.

Resetting requires a restart of the DB instance. As we could not bounce the instance immediately, we set the parameter to 1 for the time being and also removed the setting from the SPFile:

alter system set aq_tm_processes=1;
alter system reset aq_tm_processes scope=spfile;

If you want to know more about the AQ internals (and why explicitly setting aq_tm_processes to a value GREATER than 0 isn’t a good idea either), I suggest the Master Note for AQ Queue Monitor Process (QMON) (Doc ID 305662.1) for further reading.