Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: How to calibrate the CBO

Re: How to calibrate the CBO

From: Ron <support_at_dbainfopower.com>
Date: Thu, 12 Feb 2004 15:47:02 -0800
Message-ID: <lPSdna1wRqNqk7Hd4p2dnA@comcast.com>

Hello Mike,

   Absolutely agree with you. Unfortunately some individuals dominate and saturate this newsgroup with personal agendas, vendettas and Prejudices.

Regards,

  Ron
  DBA Infopower
  http://www.dbainfopower.com
  Standard disclaimer:
http://www.dbainfopower.com/dbaip_advice_disclaimer.html

"Mike Ault" <mikerault_at_earthlink.net> wrote in message news:37fab3ab.0402081141.446b3cd_at_posting.google.com... > "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 Thu Feb 12 2004 - 17:47:02 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US