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

"Robert Klemme" <shortcutter_at_googlemail.com> wrote in message news:5agr12F2nlqn8U1_at_mid.individual.net...
>
> 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

Have a look at the following:

        http://jonathanlewis.wordpress.com/2007/04/30/system-statistics/

Based on your last comment, you could do the following:

    set the db_file_multiblock_read_count to 128

Run a test (outside of Oracle) which does 8KB random reads from a very large file, then does 1MB random reads, and record the average read time. Use dbms_stats.set_system_stats to set:

    MBRC to 128
    sreadtim to the 8KB read time
    mreadtim to the 1MB read time

This should have allowed you to measure the capability of your NFS filer, and tell Oracle about it so that it factors time into its plans more appropriately.

Since the filer might do readahead on large reads in real tablescans, you might also measure the speed of doing (say) 4MB random reads, and using that time, divided by 4 as the mreadtim for 128 blocks.

(I am assuming you have a block size of 8K).

-- 
Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com

Author: Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html