Re: Which plan is better - what COST really means ?
- From: Connor McDonald <hamcdc@xxxxxxxxxxx>
- To: oracle-l <oracle-l@xxxxxxxxxxxxx>
- Date: Sun, 16 Jan 2005 09:35:37 +0000 (GMT)
Whilst the CBO obviously can have its shortcomings, my overall experience with
the CBO since 9.2
is that queries of virtually any level of complexity will still be optimized
well if the
underlying data model is appropriate and well-defined. Conversely, the
simplest of queries can be
optimized dreadfully if the model does not support the questions being asked.
Too often I see the CBO being criticised due to omission of those simple things
like declaring
null-ness, using appropriate data type and precision, referential integrity and
other such
constraints...
gigo
Cheers
Connor
--- Wolfgang Breitling <breitliw@xxxxxxxxxxxxx> wrote:
> Some context may get lost because I have to cut most of the quoting.
>
> The topic of this thread is "what COST really means" and then Karen brought
> up the question why she does not observe the desired correlation between
> cost and execution time. I was attempting to answer that question by
> pointing out some of the reasons why the optimizer's "cost" calculations
> may be off.
> In its attempt to find the best plan the optimizer needs some criterion to
> rank the different plans. The RBO used rules, the CBO uses cost expressed
> in the currency of block IO. How long it takes and how difficult that is
> wasn't part of the discussion.
> Lastly, I beg to disagree. Violation of the the optimizer's assumption of
> uniformity of data distribution can cause great havoc with the CBO's
> costing attempts. And in reality, the uniform distribution assumption and
> predicate independence assumption are very often violated. A lot of the
> problems can be avoided or mitigated by good design but if you have a
> system that is built "database agnostic", i.e. which treats the database as
> a big data dump, that's where the CBO really struggles because it has no
> guidance about the data structures due to generic design nor form the
> statistics due to its assumptions being at odds with reality.
>
> At 11:00 PM 1/14/2005, you wrote:
>
> >The units of the "cost" are like monopoly money. They help CBO to pick
> >the best strategy, but other then that, CBO cost has no meaning
> >whatsoever.
>
> Jonathan's point is exactly that the cost is not an arbitrary currency like
> "monopoly money" but has by design a real correlation to the expected
> execution time - or at least ought to. I was trying to explain why it
> sometimes (still too often) misses the mark.
>
> >Mladen Gogala
> >Oracle DBA
>
>
> Regards
>
> Wolfgang Breitling
> Centrex Consulting Corporation
> http://www.centrexcc.com
>
> --
> //www.freelists.org/webpage/oracle-l
>
=====
Connor McDonald
Co-author: "Mastering Oracle PL/SQL - Practical Solutions"
ISBN: 1590592174
web: http://www.oracledba.co.uk
web: http://www.oaktable.net
email: connor_mcdonald@xxxxxxxxx
Coming Soon! "Oracle Insight - Tales of the OakTable"
"GIVE a man a fish and he will eat for a day. But TEACH him how to fish,
and...he will sit in a boat and drink beer all day"
------------------------------------------------------------
__________________________________
Do you Yahoo!?
Read only the mail you want - Yahoo! Mail SpamGuard.
http://promotions.yahoo.com/new_mail
--
//www.freelists.org/webpage/oracle-l
Other related posts:
