Re: How to calibrate the CBO

"Mike Ault" <mikerault_at_earthlink.net> wrote in message news:37fab3ab.0402081130.2ed5a1c3_at_posting.google.com...
> > > Hmm, not a good article. There is no such thing as a silver bullet and
it
> > > doesn't actually explain what is going on particularly well. Advice
that
> > > says 'change this parameter its a silver bullet' ought to be highly
> > > distrusted.
> > >
> >
> Hi Richard,
>
> I can see you are still on your Holy crusade against Don Burleson.
>
> > Hi Niall,
> >
> > I think you're being overly kind with the above article describing it
simply
> > as "not good". It's yet another in a never ending list of appalling
articles
> > by Don Burleson.
> >
> > It has all the hallmarks of a Don "shocker"; it makes general,
> > unsubstantiated claims, it lacks any proof or evidence to support such
> > claims, it details technical inaccuracies, it's all show and not a shred
of
> > any substance. Like, I said, a typical, Don Burleson piece of tripe.
> >
>
> Interestingly, Tom Kyte and other experts have done the same thing,
> and said similar things about the parameters.

What I found difficult about the article, and I think Richard seems to be coming from a similar place, was the title, and the general tenor of the article that setting parameter X was a 'magic' solution for example

"If you are having slow performance because the CBO first_rows optimizer mode is favoring too many full-table scans, you can reduce the value of the optimizer_index_cost_adj parameter to immediately tune all of the SQL in your database to favor index scans over full-table scans. This is sometimes a "silver bullet" that can improve the performance of an entire database in cases where the database is OLTP and you have verified that the full-table scan costing is too low."

Now admittedly there are a lot of cans and mays in the above, but the gist seems to be 'if you have an OLTP database set o_i_c_a low and your whole database will work better'. It also rather suggests that ALL sql will benefit from favouring indexed access paths over FTS. I think we all know that that isn't true.

I'm also not entirely sure that Tom is entirely well served by your suggestion above. For example searching on the parameter name at asktom gives the advice below http://tinyurl.com/3x737 I did also find an article where he used an example value for this parameter of 35, but then later on states

"index caching is subtle -- less obvious

index cost adj is like hitting it on the head with a hammer, a tad more aggresive.

In reality if your system is running fine, you feel the queries are doing about the right amount of work, setting these is not something you need to do.
 you do this in reaction to poorly performing situations.

I've found three values to be most useful in most cases for cost adj

o default
o 25'ish
o 10'ish

sort of like turning up the volume.
"

I'm not sure that I read 'hitting it on the head with a hammer' as a recommendation as such.

> Most suggesting settings, including those from Metalink articals and
> other experts suggest this range.

Metalink doc id 62285.1 has some advice, and warnings on this exact parameter.

> > - "It is a numeric parameter with values from zero to 100". He can't
even
> > get this right !! The valid values are *1* to *10000* as clearly
documented
> > in the manuals. Don has the unique ability to get the simple things
totally,
> > totally wrong wrong wrong...
> >
>
> Yes, afraid it was incorrect here but with over 200 parameters (not
> including the 200+ or more undocumented) it can be easy to miss these.
> However, it should have been checked.

And I note that the article has been corrected. Not sure if any other changes were made since I made my original remarks, the paper looks different but that could be my memory failing me,

-- 
Niall Litchfield
Oracle DBA
Audit Commission UK