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

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 A. Morgan
> University of Washington
> damor..._at_x.washington.edu
> (replace x with u to respond)
> Puget Sound Oracle Users Groupwww.psoug.org

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.

If the above is not what Robert intended, then I second the book recommendations.

Robert, what is the value of OPTIMIZER_MODE?

Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc. Received on Thu May 10 2007 - 11:40:25 CDT