Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: HOw does the optimizer evaluate db_file_multiblock_read_count ?

Re: HOw does the optimizer evaluate db_file_multiblock_read_count ?

From: Richard Foote <richard.foote_at_bigpond.com>
Date: Fri, 1 Nov 2002 14:50:09 +1000
Message-ID: <gFmw9.66974$g9.188444@newsfeeds.bigpond.com>


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.
Received on Thu Oct 31 2002 - 22:50:09 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US