Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Query not using indexes

Re: Query not using indexes

From: Paul Drake <bdbafh_at_gmail.com>
Date: 9 Jul 2004 07:57:53 -0700
Message-ID: <910046b4.0407090657.42f15750@posting.google.com>


"hastenthunder" <hastenthunder_at_hotmail.com> wrote in message news:<vL_Gc.576$Ny6.1485_at_mencken.net.nih.gov>...
> Hi,
>
> A query of mine in Oracle 9i R2 chooses to perform a full table scan of 1
> million row instead of using the index.
> I'm using CBO and just computed the statistics for the table.
>
> Can someone help?
>
> Thanks

  1. please post a sample query, including a brief table description.
  2. please post the explain plan from autotrace or from a 10046 trace > tkprof. In 9.2, you can also grab the plan from v$sql_plan, which is an extremely nice feature.

I'd recommend that you check out Cary Millsap and Jeff Holt's paper on event 10046 trace:
http://www.hotsos.com/dnloads/1/10046a/
(lots of other good papers at this site)

I'd recommend that you check out Wolfgang Brietling's paper on event 10053 trace.
http://www.centrexcc.com/
Papers - "A Look under the Hood of CBO: The 10053 Event"

Even if you don't read every detail provided in the resulting trace file, it will document all of the relevant optimizer settings for your instance and session (such as the db_file_multiblock_read_count - hint, hint).

have fun.

Pd Received on Fri Jul 09 2004 - 09:57:53 CDT

Original text of this message

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