After upgrading to 10.2.0.3 using DBUA the message "WARNING: AQ_TM_PROCESSES is set to 0" begins appearing in the alert log file.
DBUA has set the aq_tm_processes initialization parameter explicitly to zero.
Fix
In 10.2, it is recommended to leave the parameter aq_tm_processes unset and let the database autotune the parameter.
Setting aq_tm_processes parameter explicitly to zero which disables the time monitor process (qmn), can disrupt the operation of the database due to several system queue tables used when the standard database features are used.
You cannot determine if aq_tm_processes is set explicitly to zero just by querying v$parameter.
A check to see if the parameter is explicitly zero is:
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;
/
If it is set to zero, it is recommended to unset the parameter.
alter system reset aq_tm_processes scope=spfile sid='*';
However, this requires bouncing the database if unable to do so
alter system set aq_tm_processes = 1;
