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: Mike Ault <mikerault_at_earthlink.net>
Date: 8 Feb 2004 11:30:16 -0800
Message-ID: <37fab3ab.0402081130.2ed5a1c3@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 Richard,

I can see you are still on your Holy crusade against Don Burleson.

> 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.
>

Interestingly, Tom Kyte and other experts have done the same thing, and said similar things about the parameters.  

> 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.

Actually, if the optimizer considers only 50 or 100 different plans rather than up to the 80000 possible with the 8i limit and the 2000 possible with the 9i limit, then it will inded be faster.

>
> - "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.
>

Most suggesting settings, including those from Metalink articals and other experts suggest this range.

> - "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 seems to be based on experience and the advice of several other experts as well as metalink.

> - "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...
>

Yes, afraid it was incorrect here but with over 200 parameters (not including the 200+ or more undocumented) it can be easy to miss these. However, it should have been checked.

> - "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.
>

Ih the section you quoted above the tone of the article was set "where the database is OLTP and you have verified that the full-table scan costing is too low" you seem to have forgetten this quote in your zeal.

> - 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.

Again withn the limits stated it makes perfect sense. I don't see you giving this simple method in your answer either.

>
> 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 !!
>

Well Richard, we are still waiting for your book...of course then we will all have to retire since it will no doubt make all paths in Oracle straight, allow even a manager to tune a database and make all other books on the subject obsolete.

> 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

Cheers,

Michael Received on Sun Feb 08 2004 - 13:30:16 CST

Original text of this message

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