From Rajendra.Jamadagni@espn.com Fri, 13 Jun 2003 10:38:11 -0700 From: "Jamadagni, Rajendra" Date: Fri, 13 Jun 2003 10:38:11 -0700 Subject: RE: Query Tuning Question - new discovery Message-ID: MIME-Version: 1.0 Content-Type: text/plain Title: RE: Query Tuning Question - new discovery A Ha ... it is refers to _B_TREE_BITMAP_PLANS variable ... it is true by default and what you see is the side effect. If you are not using BMI, set it to false.   http://tinyurl.com/e8ws for more info Raj -------------------------------------------------------------------------------- Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -----Original Message----- From: Meng, Dennis [mailto:[EMAIL PROTECTED]] Sent: Friday, June 13, 2003 11:20 AM To: Multiple recipients of list ORACLE-L Subject: 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). ********************************************************************This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you.*********************************************************************2