RE: Queue_message wait event

[Jamadagni, Rajendra]

Title: Queue_message wait event

Thanks Arup,   I also dug up some more information. I traced "select * from v$AQ;" statement and found that selecting from one of our queue tables is taking way too long.   The table is about 800M, after analysis the optimizer knows there are only 3 rows, so it doesn't use index and hence it takes way too long.   We will be dropping and re-creating it tomorrow, so let's see how it goes ...   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 !! -----Original Message----- From: Arup Nanda [mailto:[EMAIL PROTECTED] Sent: Wednesday, March 12, 2003 2:40 PM To: Multiple recipients of list ORACLE-L Subject: Re: 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 !!

********************************************************************This e-mail 
message is confidential, intended only for the named recipient(s) above and may 
contain information that is privileged, attorney work product or exempt from 
disclosure under applicable law. If you have received this message in error, or are 
not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 
and delete this e-mail message from your computer, Thank 
you.*********************************************************************2