Re: HOw does the optimizer evaluate db_file_multiblock_read_count ?

Richard Foote wrote:
>
> Hi Bass,
>
> The optimizer "evaluates" the db_file_multiblock_read_count at "face value".
> Artificially set it too high and the optimizer is tricked into thinking a
> multiblock read is more efficient than it really is.
>
> Generally, not a good idea.
>
> Note there is a maximum that it can effectively be set to.
>
> Cheers
>
> Richard
> "Bass Chorng" <bchorng_at_yahoo.com> wrote in message
> news:bd9a9a76.0210311615.56223860_at_posting.google.com...
> > Does anybody know if the cost based optimizer evaluates
> > db_file_multiblock_read_count's face value or what it
> > can actually do ?
> >
> > The background of my question is, we all know CBO's
> > behaviour may change depending on your multiblock_read_count
> > value. If it is high, it "thinks" full table scan is cheap,
> > and therefore may favor using more.
> >
> > But we also know that what you set on multiblock_read_count
> > is not necessarily what you get, because there are lots of
> > other bottlenecks, such as maxphys, your file system cache..
> > etc. For 8K block, you can set it to 128, but may only get
> > actual I/O size of 32 blocks, or in some cases, only 1,
> > depending on the file system you use.
> >
> > So the question is, when CBO evaluates this, does it use
> > the face value (which is whatever you set it to), or does
> > it use what you can actually get to determine the cost of
> > a full table scan ?
> >
> > Thanks for sharing your insight in advance.

Some info here

http://www.oracledba.co.uk/tips/mbrc.htm

hth
connor

-- 
==============================
Connor McDonald

http://www.oracledba.co.uk

"Some days you're the pigeon, some days you're the statue..."