Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: EXPLAIN PLAN : better with RULE!
You've advised the optimiser that
multiblock reads are very cheap.
Oracle makes two naive assumptions
about block accesses.
Until about 8.1.5, you could not
modify these assumptions directly,
you could only work around them.
Tim Gorman wrote the definitive paper
about the first two parameters here.
It's called 'The search for intelligent life'
at www.evdbt.com
> optimizer_index_cost_adj: 100
> optimizer_index_caching: 0
Set the second one to indicate the
typical percentage of index leaf accesses
that will be buffered. (As an approximation
you might use the cache hit ratio as a
starting point).
Set the first one to represent the fraction of time a single block read takes compared to a multiblock read. e.g. of a multiblock read takes about four times a single block read, set this to 25 (for 25%).
> db_file_multiblock_read_count: 32
> db_block_size: 16384
Check the typical size of an achieved
multiblock read, and set the
db_file_multiblock_read_count to that
value.
In this way, you are telling Oracle three very important costing factors about your system and it will do far better at getting optimal plans.
-- Jonathan Lewis http://www.jlcomp.demon.co.uk Next Seminars UK Sept Australia August Malaysia September USA x 2 November http://www.jlcomp.demon.co.uk/seminar.html Steve Mitchell wrote in message ...Received on Thu Jul 18 2002 - 18:45:34 CDT
>
>Some of my parameters are:
> optimizer_index_cost_adj: 100
> optimizer_index_caching: 0
> db_file_multiblock_read_count: 32
> db_block_size: 16384
>
>Thanks for any insight.
>
>--steve