RE: High mbrc in aux_stats$

Hi Chris,

Thanks for you reply. If I get your comment right, you think that the "auto tuned" value may be incorrect (too large). As far as I can see the autotuned value makes a lot of sense for sequential IO. I use ASM, which stripes and datafiles into 1 MB Allocation Units, so when Oracle sets the default db_file_multiblock_read_count * db_block_size =1MB, it looks to me as a good idea. Indeed I see very good sequential IO performances with this test system (10.2.0.2).

One problem there, if any, may be with the optimizer favoring too much full scans. I didn't understand your comment 'in 10gR2 either MBRC (system statistics) or a default value (8) is used'. Do you mean that the CBO makes the decision at run time and independently for each query? Do you have more details on this new feature?

Cheers,
Luca

-----Original Message-----
From: Christian Antognini [mailto:Christian.Antognini_at_trivadis.com] Sent: Sunday, March 12, 2006 11:33 PM
To: Luca Canali
Cc: oracle-l_at_freelists.org
Subject: RE: High mbrc in aux_stats$

Let me add that my comments below are based on 10.2.0.1. Eventually they fixed something on the first patch set...

>-----Original Message-----
>From: Christian Antognini
>Sent: 12 March 2006 23:24
>To: 'Luca.Canali_at_cern.ch'
>Cc: oracle-l_at_freelists.org
>Subject: RE: High mbrc in aux_stats$
>
>Ciao Luca
>
>>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).
>
>Attention, it is not the max I/O size, it is, according to the
>reference guide, the "maximum I/O size that can be efficiently
performed". It seams not that different... in reality it is!
>
>>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."
>
>This is because in 10gR2 either MBRC (system statistics) or a default
value (8) is used.
>
>>...any thoughts/experiences on using the default value for
>>db_file_multiblock_read_count on 10gR2?
>
>DON'T USE IT. Presently the "auto-tuned" value is not dependent on
>performance figures but only on a couple of other parameters.
>
>
>HTH
>Chris

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