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

On May 14, 5:59 am, Niall Litchfield <niall.litchfi..._at_gmail.com> wrote:
> 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 DBAhttp://www.orawin.info/services

No doubt that 1-5 cause costs metrics to go astray. This exactly why we should never assume lower cost = better performance.

My point is to not rely solely on cost, but also test. Looking at cost is one thing, but make sure lower = faster by testing, not assuming.

Even with adequate testing, systems are singular, so testing in one environment will never yield exactly the same result in another. It is always approximate. This is the art of performance tuning. It is a practice, not an exact science. Add in statistics for data and system dynamics, and we have to look at tolerance levels rather than hard and fast numbers. The indicator is the system response time. The measure is the percentage performance gain vs optimization $cost.

While xplan cost may point in the right direction, unit tests for cost anomalies will not show how the system will behave in the wild. So the only true measure of system performance is response time. Diagram the sequences, figure out the slowest, decide what to fix. If the sql is bad and lower cost yields better response time, then work at lowing the cost for that sql, but don't "assume" that lower cost is the answer for each and every performance issue on the system. Received on Mon May 14 2007 - 08:00:43 CDT