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).
