Re: How to calibrate the CBO

"Richard Foote" <richard.foote_at_bigpond.com> wrote in message news:<dFmVb.47654$Wa.11461_at_news-server.bigpond.net.au>...
> "Niall Litchfield" <niall.litchfield_at_dial.pipex.com> wrote in message
> news:4024cac9$0$21306$cc9e4d1f_at_news.dial.pipex.com...
> > "DBA Infopower Support" <support_at_dbainfopower.com> wrote in message
> > news:YI2dnc6W5-CDH7nd4p2dnA_at_comcast.com...
> > >
> > > Hello Rick,
> > >
> > > Tuning CBO correctly is relatively difficult task.
> > >
> > > Optimizer uses many documented and undocumented parameter that impact
> > > it's decision tree.
> > >
> > > One of the important parameters to tune is
> "optimizer_index_cost_adj".
> > >
> > > Check below link for some good recommendations:
> > > http://www.dba-oracle.com/oracle_tips_cost_adj.htm
> >
> > 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 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.
>
> Highlights of this particular shocker include:
>
> - "several parameters can adjust the CBO to make it faster". What does he
> mean by "it" ? "It" implies the CBO runs faster which is untrue in the case
> of the optimizer_index_cost_adj, which "might" if tuned correctly generate
> more efficient execution plans but will not make the CBO any faster in
> determining such plans.
>
> - "re-setting this parameter to a smaller value (between 10 to 30) may
> result in huge performance gains". Well yes, it may, but then again it may
> result is huge performance losses as well. There is nothing magical with a
> value of 10-30, there is no such "silver bullet" or special value. The
> *appropriate* value is system dependent and this dependency is based
> entirely on the relative expense of FTS I/Os vs Index I/Os. It's this ratio
> that the parameter optimizer_index_cost_adj should be set to (be it 10, 30,
> 50, 100, 150 ... etc.) and not some generalistic, silver bullet value. The
> CBO will select the "best" plan based on the specific characteristics of the
> system and schemas, not on silver bullets. Provide the CBO with inaccurate
> information and expect the CBO to get it wrong.
>
> - "this is silver bullet than 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". Firstly, setting an "appropriate" value
> for optimizer_index_cost_adj is just as valid and important in DSS/Data
> Warehouse/Hybrid systems as it is in OLTP. Also, if you have verified that
> full-table scan costing is too *high*, then changing the
> optimizer_index_cost_adj to a *higher* might be appropriate. Again, Don is
> making silly generalisations with no subsequent evidence to back things up.
>
> - "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...
>
> - "If you do not like the propensity of the CBO first_rows mode to favour
> full-table scans, you can lower the value of optimizer_index_cost_adj to 20,
> thereby telling the CBO to always favour index scans over full-table scans."
> There are so many things wrong with this one statement, I don't know where
> to begin:
>
> - Firstly, the first_rows optimizer has a propensity for index scans,
> not full table scans.
> - The optimizer_index_cost_adj is just as relevant (if not more so) with
> the other CBO modes, especially all_rows, that Don's focus on first_rows is
> misleading and plain wrong.
> - There is no magic value of 20 !! Again another total generalisation
> with no supporting evidence that is plain wrong (except for those particular
> environments where it happens to be right). Again, the *appropriate* value
> is the ratio of I/O performance of FTS vs Index scans. This BTW could be
> determined by investigating the relative performance wait statistics of
> sequential vs scattered reads.
> - Even a value of 20 doesn't *always* make the CBO favour index scans
> over FTS. There are still many scenarios where a FTS could still be
> preferred.
> - Don's assumption that favouring the CBO to always use an index scan as
> necessarily a good thing is of course entirely wrong. If it's *actually*
> more efficient to perform a FTS (from either a costing or response time
> perspective) then the FTS is the way to go, and the index scan should be
> avoided.
>
> - The whole last paragraph discusses bumping down the
> optimizer_index_cost_adj parameter and measuring the ratio of FTS which is
> entirely an invalid approach to take. It suggests that FTS are bad, which
> they're not if they're the most efficient way to go. Falsely making FTS more
> expensive by artificially increasing it's perceived cost in order to
> encourage the use of "inefficient" index plans is simply and purely a
> nonsense.
>
> There is a relatively simple and effective manner in which to set the
> optimizer_index_cost_adj parameter appropriately which clearly Don Burleson
> has absolutely no idea about as it's not once mentioned in his article (as I
> mention above). Instead, he discusses generalisations, silver bullets and
> technical inaccuracies without a single shred of evidence to support his
> claims. Welcome to the fantasyland that is Oracle tuning by Don Burleson.
> All this by someone who self proclaims to be one of the worlds leading
> Oracle experts !!
>
> All a bit sad really.
>
> And DBA Power Support, you actually recommend this rubbish ? No wonder your
> disclaimer is oh sooooo loooonnngggggg !! Shame on you ...
>
> Cheers
>
> Richard

Richard,

In addition to my other posting, let me add that Don and I tune systems on a daily basis, it is our job. He in his role with BEI, mine with my role at TUSC. We answer based on what we see works in the real world. Sometimes in our attempts to make knowledge available we may miss a fact or two, regretable but it happens.

To continue such constant harrassment of a single person you must have some hidden vendetta against him. Did he run over your dog when you were a small lad? As I understand it you post constant diatribes against his articles where ever they appear, post negative ratings for his books, and in general make a real bother of yourself. Don't you have a life other than harrassing other folks? I always find it more uplifting to find the things correct in an article than dig for every possible shade of incorrectness. If I find a glaring error, I may post an email to the author, telling them of their error, but I try not to publicly embarrass anyone.

You make allegations and claims to superior knowledge yet I don't see you actually answer the questions posed in your anxiousness to slam those who do. You and those like you are the reason I shun most online Oracle lists in favor of those with monitors.

Mike Received on Sun Feb 08 2004 - 13:41:40 CST