How to tune Oracle queue messages wait time

Posted on 2003-03-09
Medium Priority
Last Modified: 2007-12-19
I am having Oracle 8.1.7 Parallel Server Database(two instances). We are having major performance problems, then I ran the STATSPACK and found that queue nessages is having lot of wait time(97.8%). We are using Oracle Advanced Queuing.

If you have any idea how to tuning Queue Messages events, please let me know.
Question by:gvreddy365
LVL 48

Accepted Solution

schwertner earned 672 total points
ID: 8101059
Time Manager
The time manager is a background process that monitors the messages in the queue.  It provides the mechanism for message expiration, retry and delay. A parameter called AQ_TM_PROCESSES should be specified in the init<sid>.ora parameter file if you want to perform time monitoring on queue messages. This parameter can be set to values 0 or 10.  If this parameter is set to  >1, time manager processes will be created as a background processes to  monitor the messages.      
Example:  #init<sid>.ora parameter file               AQ_TM_PROCESSES=1

fact: Oracle Server - Enterprise Edition
symptom: Unexplained log activity in a database
symptom: logminer reports unsupported operations on AQ$
symptom: No jobs are running
symptom: No users are connected
symptom: Frequent log switches appear.
cause: The Database was created by DBCA ( Database Configuration Assistent)
The DBCA sets init.ora parameter:


However default value should be:

AQ_TM_PROCESSES = 0 // queue monitor is not created

AQ_TM_PROCESSES enables time monitoring of queue messages. The times can be
used in messages that specify delay and expiration properties. Values from 1 to
10 specify the number of queue monitor processes created to monitor the
messages. If AQ_TM_PROCESSES is not specified or is set to 0,then the queue
monitor is not created.

The QMNn processes monitor timing related activities such as message expiration
in Queues.If you have installed the Queued Shipping (QS) demo, Oracle may fill
up to 16M in archive logs in one day, on a database where there is no activity

What is Queue Monitor Processes (QMNn)? =======================================   SCOPE: ======   In Oracle8 Server Concepts, part# A50564-1, page 4-11, there is a description  of all background processes. The new Oracle Advanced Queuing (Oracle AQ) QMN process is not described.    Queue Monitor Processes (QMNn)  ==============================  The queue monitor process is an optional background process for Oracle  Advanced Queuing (Oracle AQ) which monitors the message queues. You can  configure up to ten queue monitor processes. These processes, like the SNPn  processes, are different from other Oracle background processes in that process  failure does not cause the instance to fail.   An Oracle instance may have many background processes; not all are always  present. The background processes in an Oracle instance include the following:    Database Writer (DBW0 or DBWn)       Log Writer (LGWR)       Checkpoint (CKPT)   System Monitor (SMON)       Process Monitor (PMON)       Archiver (ARCH)   Recoverer (RECO)       Lock (LCKn)       Job Queue (SNPn)       Queue Monitor (QMNn)   Dispatcher (Dnnn)       Server (Snnn)    Queue Monitor  ==============  The queue monitor is an optional background process that monitors messages in  the queue. It provides the mechanism for message expiration, retry, and delay  (see "Windows of Execution") and allows you to collect interval statistics (see  "Queuing Statistics").   The queue monitor process is different from most other Oracle background  processes in that process failure does not cause the instance to fail.   The initialization parameter "AQ_TM_PROCESSES" specifies creation of one or  more queue monitor processes at instance startup.   Windows of Execution =====================  You can specify that the consumption of a message has to occur in a specific  time window. A message can be marked as available for processing only after a  specified time elapses (a delay time) and as having to be consumed before a  specified time limit expires.   The initialization parameter "AQ_TM_PROCESSES" enables time monitoring on queue  messages, which is used for messages that specify delay and expiration  properties. Time monitoring must also be enabled if you want to collect  interval statistics (see "Queuing Statistics").   If this parameter is set to 1, Oracle creates one queue monitor process (QMN0)  as a background process to monitor the messages. If it is set to 2 through 10,  Oracle creates that number of QMNn processes; if the parameter is not specified  or is set to 0, then queue monitor processes are not created. The procedures in  the "DBMS_AQADM" package for starting and stopping queue monitor operations are  only valid if at least one queue monitor process was started with this  parameter as part of instance startup.    Queuing Statistics ==================  Oracle AQ keeps statistics about the current state of the queuing system as  well as time-interval statistics in the dynamic table V$AQ (GV$AQ).   Statistics about the current state of the queuing system include the numbers of  ready, waiting, and expired messages.   One or more queue monitor processes must be started (see "Windows of Execution")  to keep interval statistics, which include:            -the number of messages in each state (ready, waiting, and expired)           -the average wait time of waiting messages           -the total wait time of waiting messages    The initialization Parameter: AQ_TM_PROCESSES =============================================            Introduced:   Oracle8.0       Parameter type:   integer      Parameter class:   dynamic, scope=ALTER SYSTEM        Default value:   0  Range of values:8.0:   either 0 or 1                   8i:   0-10  Description ~~~~~~~~~~~  "AQ_TM_PROCESSES" specifies whether a time manager is created. If "AQ_TM_PROCESSES" is set to 1, then one time manager process is created to  monitor the messages. If "AQ_TM_PROCESSES" is not specified or is set to 0,  then the time manager is not created.   In Oracle 8.0, setting the parameter to a value greater than 1 results in an  error and no processes are started.     Solution References:  ====================    Oracle8 Server Concepts, Part# A50564-1 Oracle Advanced Queuing manual  

Assisted Solution

konektor earned 664 total points
ID: 8101195
set AQ_TM_PROCESSES parameter to higher value. if u set it only to 1, only one operating system proces serving queues is created and when more processes uses queue they mus wait for freeing this proces. it's similar to job_queue_processes, is u set it to one, only 1 proces serves jobs and when 2 or more jobs are to be performed int the same time, only one is and the rest of them waits to free the OS proces.

Author Comment

ID: 8102540
We have originally set AQ_TM_PROCESSES to 1, it was not having any problems. When we noticed some performance problems, we changed this parameter to 2. Nothing improved.
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!


Author Comment

ID: 8102610
We have originally set AQ_TM_PROCESSES to 1, it was not having any problems. When we noticed some performance problems, we changed this parameter to 2. Nothing improved.
LVL 11

Assisted Solution

pennnn earned 664 total points
ID: 8104641
The SNP background processes for the AQ are quite resource consuming and we have also had problems with performance because of that...
There several things you might want to look at. One of them is the propagation schedule. That really depends on how you use the queues and what your requirements are but you might want to check the latency for all your propagation schedules. The defualt value of 60 (seconds) is usually good, but you might want to do some testing and determine what the optimum value needs to be. If the real time propagation is not a requirement you might want to increase the latency. This will definitely reduce the overhead caused by the SNP processes. What we have done in some cases is reduce the latency to 0 during the night and doing some batch enqueuing and then setting it back to 60 for the daily processing (or even to a higher value if you want to).
Another thing to consider is the retention time for the messages. I don't know if that's documented but we noticed that if there are messages on the queue table, even if they are dequeued from all subscribers (state = 2; retention is > 0) then the background processes are still running and scanning the table to see if all messages are propagated. But if the queue table is truncated this overhead is gone and the performance improves a lot. This doesn't realy matter if the enqueuing happens throughout the day and there're always messages on the queue, but if the enqueuing happens in batch then it might be a good idea to wait for a while to see if all messages are propagated and deueued and then truncate the queue. Or you can set the retention time to 0 so the messages are never retained on the queue after being dequeued.
So except all the database parameters related to the queue processes, you might also want to play around and tune the queue settings as well.
Hope that helps!
LVL 13

Expert Comment

ID: 9999326
No comment has been added lately, so it's time to clean up this TA.
I will leave the following recommendation for this question in the Cleanup topic area:

Split: schwertner {http:#8101059} & konektor {http:#8101195} & pennnn {http:#8104641}

Please leave any comments here within the next seven days.

EE Cleanup Volunteer

Featured Post

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
Suggested Courses

580 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question