The new oracle dba can find here about what a queue monitor process is and what a queue monitor coordinator is and the various issues that the queue monitor coordinator process has in a RAC or non-RAC environment.The article also explains about the advantages and disadvantages of setting the aq_tm_processes parameter. you need this knowledge if your application/database uses advanced queuing.
| Queue Monitor Process: Architecture and Known Issues [ID 305662.1] | |||
|
|
|||
| Modified 23-MAR-2009 Type BULLETIN Status PUBLISHED | |||
In this Document
Purpose
Scope and Application
Queue Monitor Process: Architecture and Known Issues
References
Applies to:
Oracle Server – Enterprise Edition – Version: 8.1.7.0.0 to 11.1.0.7.0
Oracle Server – Standard Edition – Version: 9.2.0.1.0 to 11.1.0.7.0
Information in this document applies to any platform.
Purpose
In this article, we will discuss the following
1. The Queue Monitor (Q00*) and Queue Monitor Coordinator (QMNC) process architecture. These are collectively described as the QMON processes.
2. Known issues which affect these processes.
3. How to collect useful diagnostic information when problems arise with them.
Scope and Application
Database administrators of Advanced Queueing (AQ) and Streams databases.
Queue Monitor Process: Architecture and Known Issues
Queue Monitor Processes
The QMON processes are optional background processes used by Oracle Streams Advanced Queueing (AQ), Streams and a variety of other Database products which monitor and maintain all the system and user-owned AQ persistent and buffered objects. These optional processes, like the job_queue processes, do not cause the instance to fail on process failure. They provide the mechanism for message expiration, retry, and delay, maintaining queue statistics, removing processed messages from the queue table and maintaining the dequeue IOT. They also handle all the supported buffered message operations.
Pre-10g QMON Architecture
The number of queue monitor processes is controlled via the dynamic initialisation parameter AQ_TM_PROCESSES. If this parameter is set to a non-zero value X, Oracle creates that number of QMNX processes starting from ora_qmn0_<SID> (where <SID> is the identifier of the database) up to ora_qmnX_<SID> ; if the parameter is not specified or is set to 0, then QMON processes are not created. There can be a maximum of 10 QMON processes running on a single instance. For example the parameter can be set in the init.ora as follows
aq_tm_processes=1
or set dynamically via
alter system set aq_tm_processes=1;
10g QMON Architecture
Beginning with release 10.1, the architecture of the QMON processes has been changed to an automatically controlled coordinator slave architecture. The Queue Monitor Coordinator, ora_qmnc_<SID>, dynamically spawns slaves named, ora_qXXX_<SID>, depending on the system load up to a maximum of 10 per instance.
For version 10.1 onwards it is no longer necessary to set AQ_TM_PROCESSES when Oracle Streams AQ or Streams is used. However, if you do specify a value, then that value is taken into account but the number of processes can still be auto-tuned and so the number of running qXXX processes can be different from what was specified by AQ_TM_PROCESSES. If AQ_TM_PROCESSES is not specified in versions 10.1 and above, QMNC only runs when you have AQ objects in your database.
If should be noted that 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. 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 on 10g systems 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. To check whether auto-tuning is enabled or aq_tm_processes=0 do the following:
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. 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:
connect / as sysdba
alter system set aq_tm_processes = 1;
To unset the parameter:
When using a pfile:
Comment out or remove the aq_tm_processes entry, and restart the database.
When using a spfile:
connect / as sysdba
alter system reset aq_tm_processes scope=spfile sid='*';
and restart the database
Known Issues affecting QMON
Note 267137.1 QMON does not perform space management operations on the dequeue IOT in Locally Managed Tablespaces using ASSM or when using FREELIST GROUPs
Note 233101.1 Queue Monitor process Memory Consumption increases due to a Leak
Note 208563.1 Unexplained Log Activity In An “idle” Database On AQ$_QUEUE_TABLE_AFFINITIES and AQ$_QUEUE_TABLES Caused by QMNn Processes
Note 271955.1 Repeated Restarting dead background process QMNX message in the Alert Log
Note 238272.1 Procedure to Manually Purge Messages from a Single-Consumer Queue when QMON fails to do it efficiently
Note 271855.1 Procedure to manually coalesce all the IOTs/indexes associated with Advanced Queueing tables to maintain Enqueue/Dequeue performance and reduce QMON CPU usage and Redo generation
Note.251737.1 PROCESSED Messages remain in Queue Table after a Successful Dequeue
Note.341133.1 Messages not changed from Wait To Ready State in a RAC database
Note.343282.1 CPU Consumption Of Queue Monitor Processes Increases when using Retention
Note.357053.1 Ext/Mod Queue Table Ownership not Falling back to the Primary Instance in a RAC environment
Note.378247.1 PROCESSED Messages not removed from Queue Table in a RAC database after Reconfiguration
Note.393781.1 qmnc process spins / exhibits high CPU when aq_tm_processes=10
Note.394713.1 Index SYS_IOT_TOP_<N> on History IOT is very large / Qmn uses high CPU
Note.395137.1 Ext/Pub Repeated : Restarting dead background process QMNC recorded in the alert.log file
Note.453392.1 RAC Node Startups Delayed Repartitioning Queue Tables after Failover
Note.458912.1 ‘IPC Send Timeout Detected’ errors between QMON Processes after RAC reconfiguration
Note.464514.1 Messages Enqueued With a Delay Specified to an Advanced Queue in a RAC Database Are Not Dequeued Immediately After the Delay Expires
Note.564663.1 Queue Monitor Coordinator Process delays Database Opening due to Replication Queue Tables with Large HighWaterMark
Note.604246.1 Queue Monitor Coordinator Process consuming 100% of 1 cpu
Note.729535.1 ORA-00600 [1:Kwqvss], [2] reported by a Queue Monitor Slave Process causing a RAC instance to abort
Note.732743.1 Qmon Processes Are Not Removing Processed Messages or changing the state of WAITING messages
Note.738873.1 Queue Monitor Coordinator Cpu Consumption is High when AQ_TM_PROCESSES=10
Note.752708.1 Intermittently PROCESSED Messages are not removed from Queue Tables by the QMON Processes
Note.793632.1 Restarting Dead Queue Monitor Process upgrade from 9.2 to 10.2
Collecting Diagnostic Information for Troubleshooting QMON issues
If the solutions in the Known Issues section do not apply then in an ideal situation troubleshooting any issue is easier to progress with a testcase.
In the absence of this the following are some useful diagnostic steps for troubleshooting QMON issues. Typically this will be a situation in which the QMON process(es) are consuming a large amount of CPU or processed messages are not being removed.
1. For CPU consumption issues sql trace the QMON process in question by doing the following
Determine the pid of the Queue Monitor process (either qmnc or q00*), call it X
sqlplus “/ as sysdba”
oradebug setospid X
oradebug unlimit
oradebug Event 10046 trace name context forever, level 12
–Generate trace for 20 minutes
oradebug Event 10046 trace name context off
Tkprof the raw sql trace file by following Note 232443.1. Provide both the raw trace file and tkprof output to Oracle Support.
2. For issues where a queue table is not being serviced in some way then the following may be useful:
Determine the pid of the Queue Monitor processes (either qmnc or q00*), call them X, Y, etc.
sqlplus “/ as sysdba”
oradebug setospid X
oradebug unlimit
oradebug Event 10046 trace name context forever, level 12
oradebug Event 10850 trace name context forever, level 10
–10852 only applies to 10.1 onwards
oradebug Event 10852 trace name context forever, level 32
–Generate trace for 20 minutes
oradebug Event 10046 trace name context off
oradebug Event 10850 trace name context off
oradebug Event 10852 trace name context off
Repeat this tracing for all the running Queue Monitor Coordinator and Queue Monitor slave processes.
Tkprof the raw sql trace file by following Note 232443.1. Provide both the raw trace file and tkprof output to Oracle Support.
3. For investigating issues with QMON processes in a RAC environment then the following trace events are also useful
oradebug Event 10852 trace name context forever, level 128
this traces queue table ownership changes and
Event = ‘26700 trace name context forever, level 256’
which traces inter-instance IPC communication.
Note that event 26700 has a different meaning in 9.2 and should not be used.
References
NOTE:232443.1 – How to Identify Resource Intensive SQL for Tuning
NOTE:47318.1 – Init.ora Parameter “AQ_TM_PROCESSES” Reference Note
| Related
Products
Keywords
|
