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: Tuning a query in 734 rbo env. How does optimizer choose index?

Re: Tuning a query in 734 rbo env. How does optimizer choose index?

From: <morgan_at_cardume.com>
Date: Fri, 09 Mar 2001 21:23:35 GMT
Message-ID: <20010309.21233500@garoupa.cardume.com>

If you are using 7.3.4, I don't recommend that you use the cost optimize r
in a production environment.

You can check a SQL tuning tool, like Numega DBPartner (disclaimer: I work for Numega) that allows for RBO and CBO tuning.

One thing that you might do is to "disable" the other possible indexes

(like doing a dummy operation on the indexed cols, like indcol1 + 0 = xxx
or indcolchar || '' = ...)

Fernando

>>>>>>>>>>>>>>>>>> Original Message <<<<<<<<<<<<<<<<<<

On 2/27/01, 10:16:33 PM, "John" <ebektech_at_yahoo.com> wrote regarding Re:  

Tuning a query in 734 rbo env. How does optimizer choose index?:

> Thanks for your reply Myron. Unfortunately
 

> > Change to cost based optimizer.
> I would love too but I can't as it is one the custumer requirements to
 be
in
> rbo.
 

> > Change the session only into cost (or first rows) optimizer mode
 .
> I can't it's a third party application and this would affect other
queries.

> > Use a hint.
> Using a hint causes the statement to be in cbo and because there are n
 o
> stats the optimizer's plan is terrible. Once again the client doesn't
 

want
> to
> run stats.
 

> > Use a full table scan and parallel query.
> Unfortunately that table contains millions of rows and it still takes
> forever to do a full scan even in parallel.
 

> > Remove all other indexes.
> I'm going to review them tomorrow. I can't really remove any indexes
 as
> thousands of other queries are them but maybe I can combine some of th
em.

> Any other ideas?
 

> By the way what do you mean by "it chooses the index the index that ha
s
the
> highest block number for its extents"?
 

> "Myron Wintonyk" <Myron.Wintonyk_at_UAlberta.CA> wrote in message
> news:3A9C576E.7F6A4AA_at_UAlberta.CA...
> > Oracle tries to determine which index is better (from the where clau
 se).
> > If two indexes seem to be the same, then (according to oracle suppor
 t),
> > it chooses the index the index that has the highest block number for
 its
> > extents (I don't necessarily believe this description, but it doesn'
 t
 matter
> > to me).
> >
> > So, from your perspective it's random and can change with or without
 

 reboots.
> >
> > I realize you said RULE and no hints, but here are some other option
 s:
> >
> > Change to cost based optimizer.
> > Change the session only into cost (or first rows) optimizer mode
 .
> > Use a hint.
> > Use a full table scan and parallel query.
> > Remove all other indexes.
> >
> > Good luck!
> >
> > ebektech wrote:
> >
> > > I am trying to tune a query in a 7.3.4 Rule-based environment. Th
 is
 rather
> > > complex query does an index range scan and then reads the table (v
 ery
 large)
> > > by rowid. This was taking forever so I created an index containin
 g all
 the
> > > columns to be read in the table (6 columns). This drastically imp
 roved
 the
> > > query performance as the optimizer only retrieved the information
 from
 the
> > > index, the query performance was then acceptable. Unfortunately a
 fter I
> > > bounced the instance once, the optimizer didn't use this index an
 ymore.
> > > Any ideas as to how I can force the optimizer to use a specific in
 dex
> > > without going cost-based or specify hints? How does the optimizer
 

 choose
> > > the index, does it use the one with the smallest index records thi
 nking
 it
> > > would be more efficient? Or does it simply use the most recently
 used
 one?
> > > Or does it simply use the first one it finds in its catalog that m
 atches
 a
> > > certain criteria?
> >
Received on Fri Mar 09 2001 - 15:23:35 CST

Original text of this message

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