Re: HOw does the optimizer evaluate db_file_multiblock_read_count ?

As Richard says, it uses is at face value, in other words it doesn't allow for previous experience, extent sizes, hardware dependency. However, it doesn't use the actual value that you supply, but adjusts it to bump up small values and scale back large values. Then it is simply

    ceil(table HWM / scaled dfmbrc)
(plus one for oracle 9).

Sample values are

Set        Adjusted
4            4.175
8            6.589
16        10.398
32        16.409
64        25.895
128      40.865

I got these values by creating a 1M block table and then doing explain plan of a full tablescan against it, changing db_file_mbrc from 1 to 256. A simple pl/sql loop with EXECUTE IMMEDIATE does the job very neatly

If you switch to cpu_costing in 9, then the actual stored value in aux_stats$ for MBRC is used - and not adjusted before use.

--
Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

Next Seminar dates:
(see http://www.jlcomp.demon.co.uk/seminar.html )

____USA__________November 7/9   (Detroit)
____USA__________November 19/21 (Dallas)
____England______November 12/14

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html





Bass Chorng wrote in message ...


>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.