Re: EXPLAIN PLAN : better with RULE!

Thanks Lewis,

I agree it seems that the multiblock read parameters are misleading the CBO. The curious thing is that when I change some of these parameters:

ALTER SESSION SET OPTIMIZER_INDEX_CACHING = 90; ALTER SESSION SET OPTIMIZER_INDEX_COST_ADJ = 15; ..I still get the same plan from the CBO. I have goofed with the DB_FILE_MULTIBLOCK_READ_COUNT as well--trying different lower settings, and still get much slower results from the CBO than RBO.

Thanks for any help.

--steve

"Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote in message news:1027035856.15103.1.nnrp-14.9e984b29_at_news.demon.co.uk...
>
> You've advised the optimiser that
> multiblock reads are very cheap.
>
> Oracle makes two naive assumptions
> about block accesses.
>
> a) All data block visits will result in
> physical disc reads
>
> b) 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
>
>
>
Received on Fri Jul 19 2002 - 19:00:30 CDT