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

From: Robert Klemme <shortcutter_at_googlemail.com>
Date: Thu, 10 May 2007 17:11:44 +0200
Message-ID: <5agr12F2nlqn8U1@mid.individual.net>

On 10.05.2007 16:59, EscVector wrote:
> 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.

Thanks for the info! I can assure you that it is pretty well established that the plans including FIS perform significantly better than the ones that lead to a lot of individual block reads. IO waits make up for 90+% of the query execution time. This was tested via hints and now I am looking into methods that will do without hints as the IO latency is a general property of the IO system (an NFS mounted filer).

Kind regards

        robert Received on Thu May 10 2007 - 10:11:44 CDT