RE: Query Tuning Question

From: Jamadagni, Rajendra <Rajendra.Jamadagni_at_espn.com>
Date: Fri, 13 Jun 2003 10:38:11 -0700
Message-ID: <F001.005B17EE.20030613102440@fatcity.com>

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

Received on Fri Jun 13 2003 - 12:38:11 CDT