Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: HOw does the optimizer evaluate db_file_multiblock_read_count ?
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