Richard, Good job....
"Richard Foote" <richard.foote_at_bigpond.com> wrote in message news:<6O3Wb.50606$Wa.46800_at_news-server.bigpond.net.au>...
> 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 - 17:01:07 CST
