"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 - 02:43:53 CST
