Re: AQ query from dequeuw procedure not using index -URGENT

It's oracle AQ internal query so cant change it. I have already tried it without the hint and it does use the index but again can't change the query. ----- Original Message -----
To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com> Sent: Monday, December 09, 2002 12:49 PM

>
> 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 <ORACLE-L_at_fatcity.com>
> j_at_hds.com> cc:
> Sent by: Subject: RE: AQ query from
dequeuw procedure not using index -URGENT
> root_at_fatcity.c
> 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: Rajesh.Rao_at_jpmchase.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).
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: orafaq
  INET: shgarg_orafaq_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).