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: Query Tuning Question - new discovery

RE: Query Tuning Question - new discovery

From: Barbara Baker <barbarabbaker_at_yahoo.com>
Date: Fri, 13 Jun 2003 10:36:51 -0700
Message-ID: <F001.005B17D1.20030613100952@fatcity.com>


not necessarily.

can you post the lines immediately above those you've provided? (call, count, cpu, elapsed, etc.)

> It looks like 'bitmap conversion to rowids' is the
> hogger.

Anybody know what this implies? Should I try
> dropping and recreating the index as b-tree? We
> don't have an identical test system here so I need a
> 'warm and fuzzy' before doing that in our
> production.
> Dennis
>
> 0 SORT GROUP BY
> 0 NESTED LOOPS
> 0 NESTED LOOPS
> 0 NESTED LOOPS
> 0 HASH JOIN
> 0 HASH JOIN
> 7 INDEX RANGE SCAN (object id 44819)
> 0 NESTED LOOPS
> 156 NESTED LOOPS
> 9 HASH JOIN
> 2 TABLE ACCESS FULL REG_MGR
> 9 TABLE ACCESS FULL SHIPTO_SALESTYP
> 164 TABLE ACCESS BY INDEX ROWID
> CUST_SHIPTO
> 164 INDEX RANGE SCAN (object id
> 447931)
> 231 TABLE ACCESS BY INDEX ROWID
> INVC_LINE
> 1323618 BITMAP CONVERSION TO ROWIDS
> 346 BITMAP INDEX SINGLE VALUE
> 0 TABLE ACCESS FULL SALESREP_DTL
> 0 TABLE ACCESS BY INDEX ROWID MTL
> 0 INDEX UNIQUE SCAN (object id 46433)
> 0 TABLE ACCESS BY INDEX ROWID CUST_SOLDTO
> 0 INDEX UNIQUE SCAN (object id 89347)
> 0 TABLE ACCESS BY INDEX ROWID
> INVC_LINE_ATTRB
> 0 INDEX UNIQUE SCAN (object id 43441)
>
> -----Original Message-----
> Sent: Thursday, June 12, 2003 3:41 PM
> To: Multiple recipients of list ORACLE-L
>
>
> has anythign changed in the table? inserts, updates,
> deletes? if so considering doing a move on the table
> to rebuild it and possibly rebuilding the indexes in
> question.
>
> have you gather statistics lately? Is it using the
> same plan it was using a fwe weeks ago?
>
>
> >
> > From: "Meng, Dennis" <[EMAIL PROTECTED]>
> > Date: 2003/06/12 Thu PM 03:54:59 EDT
> > To: Multiple recipients of list ORACLE-L
> <[EMAIL PROTECTED]>
> > Subject: Query Tuning Question
> >
> > Hi fellow DBAs,
> > This is kind of the follow-up of my last E-mail on
> wait event.
> > I have a query that is taking hours to complete
> and the plan looks ok. While one of the tables is
> huge (267mil rows) it is being accessed using one of
> its indexes.
> > I recorded some stats from v$session_wait while
> the query is running to see which segment is query
> is hanging up on and the result is the big table
> with 267mil rows.
> > Funny thing is, according to the user community,
> this query took only minutes to run couple of weeks
> ago.
> > What could be the cause of this wait? When index
> is being used, oracle will go directly to the data
> block and retrieve the data, which should be very
> efficient correct?
> >
> >
> > TIA
> >
> > Dennis
> >
> >
> >
> >
> >
> --
> Please see the official ORACLE-L FAQ:
> http://www.orafaq.net
> --
> Author: Meng, Dennis
> 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).


Do you Yahoo!?
Yahoo! Calendar - Free online calendar with sync to Outlook(TM). http://calendar.yahoo.com
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Barbara Baker
  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).
Received on Fri Jun 13 2003 - 12:36:51 CDT

Original text of this message

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