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.

1. All data block visits will result in physical disc reads
2. A multiblock read is just as quick as a single block read.

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 ...


>

>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