RE: High mbrc in aux_stats$
- From: "Luca Canali" <Luca.Canali@xxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Sun, 12 Mar 2006 19:29:58 +0100
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@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Mladen Gogala
Sent: Saturday, March 11, 2006 1:45 AM
To: Brandon.Allen@xxxxxxxxxxx
Cc: oracle-l@xxxxxxxxxxxxx
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
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
