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: Richard Foote <richard.foote_at_bigpond.com>
Date: Mon, 09 Feb 2004 11:07:02 GMT
Message-ID: <qRJVb.48920$Wa.8907@news-server.bigpond.net.au>


Hi Mike

Comment embedded.

"Mike Ault" <mikerault_at_earthlink.net> wrote in message news:37fab3ab.0402081130.2ed5a1c3_at_posting.google.com...
>
> I can see you are still on your Holy crusade against Don Burleson.

No actually. I'm simply highlighting the numerous errors and technical inefficiencies of (yet another) article by DB that was posted by someone else as incorrectly containing "good recommendations". I have absolutely nothing against DB, I do have a lot against the quality of many of his articles. There is a very big difference which you seem incapable of appreciating.

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

I really don't care what others may or may not have said. I'm expressing my personal *technically focused* view of this particular article. It's bloody awful and I simply explained why.

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

Read again how Don has grouped and described the various optimizer parameters and read again how I explained how the optimizer_index_cost_adj parameter does not effect the speed of the optimizer. Optimizer_max_permutations, yes, optimizer_search_limit, yes, but *not* the *optimizer_index_cost_adj* parameter (nor the optimizer_index_caching parameter for that matter). Your suggestion above is therefore wrong.

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

Trust me, depending on Metalink these days for "suggestions" is not something I would personally recommend or base an article on. Regarding what other "experts" suggest, I don't know of many who suggest that the optimizer_index_cost_adj is a "silver bullet", should just be set to "between 10-30" or just "20" with no examples or evidence to support such claims. In fact "experts" that I'm aware of actually provide a "method" by which the *actual* appropriate value for each and every database can be *accurately* determined *without* any *guess work*. As suggested in another post by Jonathan, why don't you look a Tim Gorman's excellent article on the CBO, see how it's written and how it qualifies and provides supporting evidence in his conclusions and compare it to Don's effort. The difference is simply remarkable.

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

You have an unfortunate stutter ...

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

Like I said before, setting an appropriate value for the optimizer_index_cost_adj parameter is just as important in non OLTP environments. Like I said before, setting an appropriate optimizer_index_cost_adj value is just as important if the FTS costing is too high. Like I said before, narrowing the focus in this manner is wrong, an appropriate value should be set no matter the situation. And setting an appropriate value is not a matter of guess work (hummm, try 28 ...), it should be carefully calculated and accurately determined, like I said before.

The fact Don suggests otherwise is why this article is so bloody awful.

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

No it doesn't make perfect sense !! Bumping down the parameter in an adhoc, guess work, see if this works, nah let's try another value manner in a production system is a nonsense. The potential havoc such a method could cause is beyond belief. Like I said before, determine the respective costs of FTS (db_file_scattered_read waits) and index scans (db_file_sequential_read waits) to determine the *actual* respect costs of both I/O operations. No guess work, simply use the particular information provided in your particular environment to set the appropriate particular value for this parameter. Did Don mention a single word on this. No.

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

Interestingly, not a single comment on whether my argument has any technical merit, whether it more accurately describes how to set this parameter than Don's suggestion of "between 10-30". Instead you make a somewhat snide reference to the fact I haven't written a book. You actually made this type of comment previously when someone else had the audacity to question something you wrote. Are you somehow suggesting that those "almighty" enough to have written a book are somehow beyond approach and should be given some "almighty" privilege for their work not be to critically anlaysed ? Or are you suggesting those of us "low types" that have not written a book should not have the right to critically analyse the works of the "almighties" and that somehow our technical comments are inferior or just plain invalid as a result?

If so, you are sooooooo sadly mistaken ...

Writing a book does not give anyone the right to publish work that is technically suspect or plain wrong *without* the possibility of subsequent critical review and comment. And those that have not written a book have every right to make such reviews and (I suggest you sit down for this as it could come as a bit of a shock) provide technical comment that perhaps is superior to those "almighties" or perhaps makes comments that prove "almighties" wrong. Gasp, horror, ....

If it would make you happy, how about I compile all the various writings I've made in this newsgroup, on metalink, on the Oracle-L mail list, User Groups, Oracle World presentations, etc. etc., edit them and publish them under the title "The Complete Works of Richard Foote (With Occasional David Bowie References), Newest In The Order Of The Almighties". Then perhaps, my comments might somehow become validated and approved by other "almighties". And I'll tell you what. If ever such a book were to be published, I would suggest it would be more technically accurate and informative than many Rampart books already on the market.

Your insinuation about me not having published a book I afraid comes across as being elitist and sadly a little pathetic :(

Like I said before, all a bit sad really.

Richard Received on Mon Feb 09 2004 - 05:07:02 CST

Original text of this message

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