Re: Queue_message wait event

[Arup Nanda]

Title: Queue_message wait event

Raj,   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. DB is 9202 aq_tm_processes = 5 job_queue_processes = 4 _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 !!