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

On 10.05.2007 18:40, Charles Hooper wrote:
> On May 10, 12:16 pm, DA Morgan <damor..._at_psoug.org> 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.

Daniel, I am not sure why you call this a "weak assumption". Of course assumed IO cost is not the only variable that the CBO takes into account - but I don't believe that it will ignore it. And the fact that the chosen plan influences performance seems to be well established.

> I believe that I read Robert's comment differently than you. A 10053
> trace seems to indicate that the Cost Based optimizer in Oracle
> determines the "best" plan based on the "assumed IO cost" - a plan
> with a higher "assumed IO cost" is discarded as soon as the plan
> exceeds the previously generated "assumed IO cost" for a previously
> generated plan. If Oracle is determining the actual cost incorrectly,
> that will potentially drive Oracle to produce a less than ideal plan,
> that will then adversely affect performance. It appears that Robert
> would like to correct Oracle's assumed IO cost so that it
> automatically generates appropriate plans (most of the time). I
> apologize to Robert if I misunderstood his comment.

No, you're spot on!

> If the above is not what Robert intended, then I second the book
> recommendations.
>
> Robert, what is the value of OPTIMIZER_MODE?

all_rows

Thanks to all for the suggestions and feedback! It'll take me some time to look into Jonathan suggestions.

Kind regards

        robert Received on Fri May 11 2007 - 03:00:21 CDT