Re: EXPLAIN PLAN : better with RULE!

Hi Steve,

Just on the DB_FILE_MULTIBLOCK_READ_COUNT (DFMRC).

This value is really important to the optimizer doing it's job right and to the database running efficiently. It needs to be set to the *correct* value and this value is totally dependent on how the O/S performs it's physical reads. An O/S will read 'X' amount of data from disk when performing an I/O, the DFMRC needs to be set to this value.

When performing a read through an index, the O/S will deliver 'X' amount of data but Oracle will only use the block size amount. When performing a FTS, the O/S will deliver 'X' amount of data, you want to tune the DFMBRC to use *all* of this data.

Setting the DFMBRC lower than 'X' will result in doing unnecessary I/O's because Oracle doesn't make effective use of the data the O/S is providing. And the FTS becomes less efficient meaning inappropriate index reads are being performed. Setting the DFMBRC higher than 'X' and Oracle will still only be able to use 'X' amount of data that it has been given *BUT* the optimizer is tricked into thinking a physical I/O is more efficient than it really is and incorrectly favours the FTS.

The correct value for DFMBRC is 'X'. No more, no less. So what is 'X'. Well it depends on your O/S, whether you are using a file system or raw devices, how the I/Os have been tailored in your environment, etc. I would recommend a visit to www.ixora.com.au where Steve Adams discusses this in much detail (and has a script you can run to determine your correct value of 'X'). An excellent read.

Hope this helps a tiny bit.

Regards

Richard
"Steve Mitchell" <stevem_at_hdcsi.com> wrote in message news:yK1_8.2545$yK4.171084961_at_newssvr13.news.prodigy.com...
>
> 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 Sat Jul 20 2002 - 08:54:25 CDT