Shaleen,
The use of FIRST_ROWS hint with an order by clause is ridiculous. The hint
is ignored.
Raj
John Kanagaraj
<john.kanagara To: Multiple recipients of list ORACLE-L
<[EMAIL PROTECTED]>
[EMAIL PROTECTED]> cc:
Sent by: Subject: RE: AQ query from dequeuw
procedure not using index -URGENT
[EMAIL PROTECTED]
om
December 09,
2002 02:49 PM
Please respond
to ORACLE-L
Shaleen,
Have you considered using Outlines?
John Kanagaraj
Oracle Applications DBA
DBSoft Inc
(W): 408-970-7002
So WHO is the Reason for the Season?! Write me for details!
** The opinions and statements above are entirely my own and not those of
my
employer or clients **
-----Original Message-----
Sent: Monday, December 09, 2002 10:04 AM
To: Multiple recipients of list ORACLE-L
All,
We are using a third party application (Retek Warehouse management) which
uses AQ as communication mechanism between processes. The problem we are
facing is that the AQ query behind Dequeue is doing a full tablescan on
queue table which is causing about 5Billion logical reads/day and is
bringing the DB to its knees.
Following is the query which I gleaned from tracing the process
select /*+ FIRST_ROWS */ qt.rowid, qt.msgid, qt.corrid, qt.priority,
qt.delay, qt.expiration, qt.retry_count, qt.exception_qschema,
qt.exception_queue, qt.chain_no, qt.local_order_no, qt.enq_time,
qt.time_manager_info, qt.state, qt.enq_tid, qt.step_no from
PAR3214.GENERICTCP qt where q_name = :1 and state = :2 order by q_name,
state, enq_time, step_no, chain_no, local_order_no for update skip locked
The procedure call is following
DBMS_AQ.DEQUEUE( 'par3214.' || qname, queueopts, msgprops, msg_obj, msgid
);
Since this is an internal query, I can not change the HINT ( Itested that
removing the hint drops logical reads from 2400 to 3).
Any ideas?
Thanks
Shaleen
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author:
INET: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
