Re: why does CBO not use available indices

Hello again,

First of all, thanks for all your answers. I now have a much better overview on the possibilities to "tune" the optimizer.

We use Oracle8.1.7 and thats what compatibitiy is set to. Our data model contains a network of about 40 tables and you can guess there are a lot more statements than just the one I've mentioned in the first posting. Due to the performance problems with the given statement we startet testing with more and more data and everytime we've updated statistics the schema the behaviour of one or more statements has changed. I thought Oracle is a professional database with proven technologies but IMHO the CBO is a nightmare, at least in Oracle8i.

Well, we finally managed to copy outlines with a good execution plan from one database to another just to survive until the next release. We are now rewriting the application to use views whereever more than one table is involved in a select. So we get at least the chance to change view definitions later on to improve the optimizer hints. This is of course hard work, but seems more reliable that letting the optimizer choose the strategy.

> The alternative, of course, is to design the
> application properly - but that's too much
> like hard work ;)

The bigger the model the more hard work :(

> > Apart from gambling on a particular combination of
> > hints always doing the same across Oracle versions.
> > Far from guaranteeed this last one. I'm actually
> > surprised Oracle suggests it in their performance
> > book. They are notorious for silently removing
> > the effects of a particular hint on a point release...

That is just what I needed :-(

> >
>
> I think 99% of the problem there has usually
> been the fact that very few people understand
> how hints work in the first case, so of course
> their effects are not stable when they are used
> incorrectly.

Am I so wrong to think that a good select strategy for a specific statement will stay good even when more data are added (presumed that size relations between tables are known for the application and won't change)? Even if hints are not that easy to understand, I got the impression that they are the best way to keep things under control.

> > Of course, cloning stats is no insurance that plans
> > will be stable across databases. But it's darn near
> > close. Have yet to see one case where it didn't work.

You can't always guarantee that admin users won't destroy your application statistics.

> <skipped> - but it is
> true that Oracle 10 has the facility for collecting better
> information about the data, and therefore creating
> descriptions of better execution plans - and that's not
> really something I would call a 'fix', it's more a case
> of a perfectly rational trade: you get better plans when
> you have better information to start with (GIGO).

Well, I'll get a presentation next Monday, but I've lost trust in automatic optimization.

> > I'm reminded of the disastrous effects in 8.* of setting
> > optimizer default to FIRST_ROWS: imp and exp performance
> > goes out the window. So do some dictionary operations.

Are there more disadvantages with that. Out application uses this parameter, because low imp and exp performance is tolerable, but slow GUI operations are not.

> I rather liked Connor's comments - take advantage of
> hints to get ideas about WHY the optimizer isn't doing
> what you expect it to do, and then fix the root cause in
> an appropriate manner.

We are about to prepare our application for that...

Thanks again for all of your contributions, Anke Received on Fri Oct 10 2003 - 14:24:38 CDT