RE: High mbrc in aux_stats$

Hi,

On a related note, Oracle 10gR2 has changed the default value on db_file_multiblock_read_count to match the system I/O max size (on my test Linux DBs with 8K block I ended up with db_file_multiblock_read_count=128).
Oracle's documentation seems to me somewhat cryptic on this. First they say:

"Even though the default value may be a large value, the optimizer will not favor large plans if you do not set this parameter. It would do so only if you explicitly set this parameter to a large value."

But they go on with more 'traditional' advice:

"Online transaction processing (OLTP) and batch environments typically have values in the range of 4 to 16 for this parameter. DSS and data warehouse environments tend to benefit most from maximizing the value of this parameter. The optimizer is more likely to choose a full table scan over an index if the value of this parameter is high."

...any thoughts/experiences on using the default value for db_file_multiblock_read_count on 10gR2?

Cheers,
L.

-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Mladen Gogala Sent: Saturday, March 11, 2006 1:45 AM
To: Brandon.Allen_at_OneNeck.com
Cc: oracle-l_at_freelists.org
Subject: Re: High mbrc in aux_stats$

On 03/10/2006 06:44:25 PM, Allen, Brandon wrote:
> Anybody seen this before?
>
>
> Notice the MBRC figure of 97 even though I have
db_file_multiblock_read_count=16. I searched Metalink for bugs and known issues but no luck. I guess the next step is to peform a 10046 trace and check the p3 value for all the 'db file scattered reads', but I figured I'd check to see if any of you have already seen this before I go to that trouble.
>
> Thanks,
> Brandon

Brandon, this setting IS trouble. Oracle will use DB_MULTIBLOCK_READ_COUNT batches but CBO will set the price for full table scan as if it was possible to read 97 blocks at once. Price of full table scan will be very low, lower then the price of a second hand Kia at your local Kia sales event, which means that you will have a lot of them. CBO will not consider index path as a viable path, which is great if the only things you're running are reports from a DW or a data mart. If, on the other hand, the database in question is an OLTP database, then I, as a foreigner who speaks poor English, cannot describe the situation without using the f-word. I doubt that Steve Adams would have enough undersanding to allow me that liberty.

--
Mladen Gogala
http://www.mgogala.com

--
http://www.freelists.org/webpage/oracle-l


--
http://www.freelists.org/webpage/oracle-l