Skip to content

QMON Architecture Pre-10g and post-10g – advanced knowledge article for the new oracle dba




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


  • Oracle Database Products > Oracle Database > Oracle Database > Oracle Server – Enterprise Edition
  • Oracle Database Products > Oracle Database > Oracle Database > Oracle Server – Standard Edition

Keywords


AQ_TM_PROCESSES; LOCALLY MANAGED TABLESPACES; QMN; QMNC; QMON; RECONFIGURATION

 

 

VN:F [1.9.22_1171]
Rating: 10.0/10 (1 vote cast)
VN:F [1.9.22_1171]
Rating: 0 (from 0 votes)
QMON Architecture Pre-10g and post-10g - advanced knowledge article for the new oracle dba, 10.0 out of 10 based on 1 rating

Post a Comment

You must be logged in to post a comment.