Oracle-L: Re: Queue_message wait event

From: Arup Nanda <orarup_at_hotmail.com>
Date: Wed, 12 Mar 2003 11:40:04 -0800
Message-ID: <F001.00567F3B.20030312114004@fatcity.com>

Queue_message wait eventRaj,

I assume you are using persistent queues. Each queue resides on a queue table that is nothing more than a normal table subjected to the same problems. By default the queue tables are created with INITRANS 1 and FREELISTS 1, like regular table.

You have to increase these parameters. Assuming your queue table is MYQT, you would simply alter table myqt initrans 10 storage (freelists 13).

Next, make sure you have only one queue (apart from the exception queue) in each queue table. Oracle docs state that if the payload of soem queues are the same, they should be in the same queue table. But in practice, it's better to leave them in separate tables to alleviate transaction related problems.

Next, you should move these queue tables into some less active data files, if that is possible. This may sound incredible, but Oracle did a lousy job in the queue implementation where they used a regular table to hold the messages. So a dequeue is nothing more than just selecting and deleting from the queue table.

Hope this helps.

Arup

Original Message ----- From: Jamadagni, Rajendra To: Multiple recipients of list ORACLE-L Sent: Wednesday, March 12, 2003 12:24 PM Subject: Queue_message wait event

Hi all,

Does anyone know how to tackle this one? Developers are complaining that (it appears) the de-queuing is slow.

    a.. DB is 9202 
    b.. aq_tm_processes = 5 
    c.. job_queue_processes = 4 
    d.. _job_queue_interval = 3

SQL> select * from gv$aq where qid in (44673,42358);

QID WAITING READY EXPIRED TOTAL_WAIT AVERAGE_WAIT

---------- ---------- ---------- ---------- ------------ 42358 0 1 0 0 0 44673 0 11 0 1321 146.777778

The average_wait column ... is it micro|centi|mega seconds?? Also how to investigate the de-queuing problems? I couldn't find a single hit on queue_message event on metalink, even an advance search.

Thanks in advance for your help
Raj

Rajendra dot Jamadagni at espn dot com Any views expressed here are strictly personal. QOTD: Any clod can have facts, having an opinion is an art !!

--

Please see the official ORACLE-L FAQ: http://www.orafaq.net
--

Author: Arup Nanda
INET: orarup_at_hotmail.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Received on Wed Mar 12 2003 - 13:40:04 CST