Where did we leave DB when he came here last ?
"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
>
>
Received on Sun Feb 08 2004 - 11:35:06 CST
