Re: How to calibrate the CBO

Hi Niall

Comments embedded

"Niall Litchfield" <n-litchfield_at_audit-commission.gov.uk> wrote in message news:40278492$0$7062$ed9e5944_at_reading.news.pipex.net...
>
> 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

Exactly !!

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

Exactly !!

The idea that simply setting it lower will magically tune your database is a nonsense. The idea that setting it lower will "immediately tune all of the SQL in a database" is a nonsense. The idea that lowering the value will automatically make all your SQL "favour index scans" is a nonsense. The idea that all those FTS that may have been converted to an index scan are necessarily a good thing and will result in faster SQL is a nonsense. The idea that the optimizer_index_cost_adj parameter is a "silver bullet" is a nonsense.

Therefore I suggest that the entire article is a nonsense.

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

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

I had a real laugh with this. When I followed your link and read what Tom had to say, this was the very first thing he says in the thread: "they are stating the obvious, the obvious being there are no silver bullets" !! It's hysterical. Tom goes on to say "nothing is entirely good, nothing is entirely bad". In the defence of his bubby Don, the only expert Mike mentioned who might agree with the principle of what Don has written has quoted exactly the opposite advice. It would be interesting to note Tom's reaction if he knew that he was being made a "reference" for this article.

Game, Set, Match is this whole debate ...

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

Exactly !!

The reference note on Metalink regarding this parameter quotes "Modifying this parameter could significantly degrade the performance of some queries if the perceived index cost is reduced to an unrealistic level and indexes are chosen when other access methods are more appropriate."

Does Don when discussing his silver bullet mention this tiny yet perhaps significant point. No.

>
> > > - "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,
>

I've some good news !! As you've noted, overnight, the article has been modified and the above error has been corrected. Obviously Mike has more influence over DB than I've ever had. Unfortunately, the whole article really needs to be re-written or pulled entirely as this cosmetic change doesn't change the main issues as discussed.

I've also noted that overnight, the embarrassing mistake and syntax errors regarding pctfree with ASSM that I mentioned in my other post has also been corrected.

So things are moving in the right direction and hopefully DB will be motivated enough to improve the quality of his output and take on board suggestions and improvements others might make.

Who knows, maybe one day, I might even be thank for it rather than being publicly insulted ...

Cheers

Richard Received on Tue Feb 10 2004 - 06:05:22 CST