RE: Query Tuning Question - new discovery

I did a sql trace and tkprof and here is the output. 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).