Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Queue_message wait event

RE: Queue_message wait event

From: Jamadagni, Rajendra <Rajendra.Jamadagni_at_espn.com>
Date: Wed, 12 Mar 2003 13:34:38 -0800
Message-ID: <F001.0056836C.20030312133438@fatcity.com>


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-----
Sent: Wednesday, March 12, 2003 2:40 PM
To: Multiple recipients of list ORACLE-L

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

Hi all,

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

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: Jamadagni, Rajendra
  INET: Rajendra.Jamadagni_at_espn.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 - 15:34:38 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US