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: Parameter to influence Oracle's Idea of IO Cost?

Re: Parameter to influence Oracle's Idea of IO Cost?

From: Niall Litchfield <niall.litchfield_at_gmail.com>
Date: 14 May 2007 02:59:44 -0700
Message-ID: <1179136784.001397.39590@q75g2000hsh.googlegroups.com>


On May 12, 10:54 pm, EscVector <J..._at_webthere.com> wrote:
> On May 12, 3:31 pm, Niall Litchfield <niall.litchfi..._at_dial.pipex.com>
> wrote:
>
>
>
> > DA Morgan wrote:
> > > Robert Klemme wrote:
>
> > >>> What does cost have to do with response time?
> > >>> Are you looking to speed something up or is this just like "tuning the
> > >>> cache hit ratio".
>
> > >> assumed IO cost => Plan => Performance
>
> > >> where "=>" means influences.
>
> > >> robert
>
> > > A very weak assumption. I would recommend the books written by Tom Kyte,
> > > Cary Milsap, and Jonathan Lewis as therapy.
>
> > I'm not sure that I would call it a very weak assumption, in fact I'd
> > call it really quite a good assumption. Up until very recently Oracle's
> > costing model was based pretty much entirely on attempting to cost
> > things in units of IO. Now these days there are also attempts to cost
> > the CPU resources being used if system stats have been gathered, but
> > never the less IO cost is a very large contributor to most calculated
> > costs, the cost drives the plan which is adopted and plan choice can
> > hugely influence performance.
>
> > --
> > Niall Litchfield
> > Oracle DBAhttp://www.orawin.info/services
>
> Does lower cost mean faster response time every time? I would say the
> assumption lies therein. I've seen many cases where developers work
> solely from cost and find that a much higher cost plan run magnitudes
> faster.

The answer to the first question is clearly no. However, the aim of the CBO is surely, to estimate the plan with the fastest response time for the desired optimizer_goal. To the extent that lower cost = higher response time I would suggest that this is likely to be due to one or more of the following (in rough order of likelihood IMO)

  1. 'bad' stats
  2. effect of specific data values
  3. missing information for the cbo (no constraints etc).
  4. poor CBO assumptions (eg filter/predicate independence).
  5. bugs

On the other hand if your developers really do look at the cost of specific queries and tune accordingly they are much more sophisticated than those I meet :(

Niall Litchfield
Oracle DBA
http://www.orawin.info/services Received on Mon May 14 2007 - 04:59:44 CDT

Original text of this message

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