Re: How to calibrate the CBO

Crikey Richard! Tell us what you really think!!

An excellent analysis of, as you put it, "tripe".

Better brace yourself for the inevitable flood of "this is libel" emails from said Guru Don of Burleson Plaza, though.

Regards
HJR "Richard Foote" <richard.foote_at_bigpond.com> wrote in message news:dFmVb.47654$Wa.11461_at_news-server.bigpond.net.au...
> 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 - 12:26:53 CST