Re: why does CBO not use available indices

>
> I really dislike your suggestion 4,

That's why I put it in last order of desirability.

> and I'm not
> too keen on option 1 either.

And yet it's one of the few reliable ways a 3rd party app writer has of ensuring that a given plan for a given table access in a given application will always be selected by Oracle. Unfortunately, the DB2 "binding" is not (yet) available in Oracle.

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...

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. Apart from obvious things like widely different DBFMR.

Then we have 10g where the actual executing statement can be "re-written" with new hints. That is fine, although far from an optimal way of fixing this problem. But I doubt very much the OP was talking about that version.

> Fixing a problem
> by kicking it until it slinks away bloody and
> bruised is not a strategic solution - it's only
> going to bounce back later, probably at the
> most awkward possible moment.

Isn't that what changing global optimizer performance parameters to fix ONE specific problem is all about? And yet it's been done ad-nauseum.

I'd rather have a specific problem get a specific solution which will NOT easily create a bucket load of others. That is not what one gets when fiddling with optimizer_* parameters. They are by their very nature global.

By that I mean they affect *all* SQL, not just the problematic one. One setting might fix one problem and be a disaster for other apps running on the same DB.

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.

I was of course assuming general purpose databases. Rather than systems that only run one application with a limited number of tables. Those behave differently.

> > > Are there any other options left?
> >
> > Besides the other excellent replies, and in order of trial:
> >
> > 1- Use DBMS_STATS to get the stats off the tables wit few rows.
> > Snapshot that value and use it to set the stats of the fully
> populated
> > tables to what they were before full loading.
> > 2- optimizer_index_caching. Set it between 80-90 and see if it
> helps.
> > 3- remove histograms and use DBMS_STATS to gather the statistics.
> > 4- remove stats altogether from ONE of the tables. I'd say TP.
> >

-- 
Cheers
Nuno Souto
wizofoz2k_at_yahoo.com.au.nospam
"Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote in message news:bm342j$jj2$1$8300dec7_at_news.demon.co.uk...