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

On May 10, 4:44 am, Robert Klemme <shortcut..._at_googlemail.com> wrote:
> On 10.05.2007 04:10, EscVector wrote:
>
>
>
> > On May 9, 6:48 am, Robert Klemme <shortcut..._at_googlemail.com> wrote:
> >> Hi,
>
> >> this is on 10.2.0.1.0. I think I remember that there is a parameter
> >> that will affect how Oracle costs IO but can't seem to find it (searched
> >> DB Reference, Performance Tuning Guide, PL/SQL Package Reference, Web).
> >> Does my memory fail me?
>
> >> Do you know other ways to influence how Oracle costs single block reads
> >> vs. multi block reads (apart from DB_FILE_MULTIBLOCK_READ_COUNT and
> >> hinting that is)? Thank you!
>
> >> Kind regards
>
> >> robert
>
> > 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

So this is like tuning the cache hit ratio.

A larger plan cost does not necessarily mean worse response time. I see examples every day where a high cost plan out performs a lower cost plan.

This is why I ask about Response Time.

It is all well and good to analyze at intervals if the understanding is that the analysis creates a new system each time. Plans can and will change with each new analysis. Plans will change with each setting tweak. This introduces lots of instability.

All necessary settings and analysis is easily performed via ALTER SESSION and tracing so that no assumptions are necessary.

I'm a Method-R proponent. "Tuning" by assumption is as good as tweaking the cache hit ratio.

Rather than working with unknowns, start by identifying if the system needs to be optimized, determine bottlenecks, and then work to understand and mitigate the slowness.

Working on non-bottlenecks can lead to CTD and impact your system in non-good ways. Don't guess or assume, determine the worst bottleneck and establish the optimization goal prior to start. Work towards the goal.

A good book:
Optimizing Oracle Performance
http://www.bookpool.com/sm/059600527X

Notice the title does not include the word "Tune" which refers to a single finite piece. It uses optimize and refers to the entire system, soup to nuts, by focusing on response time and working on only on the processes that need optimization.

If I/O latency or single-block reads is actually the problem, then ok, but make sure that it is before spending lots of time. Also make sure you check out http://www.bookpool.com/sm/1590596366 Cost-Based Oracle Fundamentals. Chapter 2 has some good information on cost. Received on Thu May 10 2007 - 09:59:02 CDT