RE: High mbrc in aux_stats$

Ciao Luca

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

Large I/O size is not always good for performance. Since I expect that such a feature finds out what the optimal I/O size is, the 10.2.0.1 implementation makes no sense.

>Indeed I see very good sequential IO performances
>with this test system (10.2.0.2).

Presently I didn't test it with 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?

With system statistics without workload the CBO uses a fixed value (which in my tests was always 8) instead of the auto-tuned db_file_multiblock_read_count.

With system statistics with workload the CBO uses MBRC from AUX_STATS$.

>Do you have more details on this new feature?

Not really. Some observations I did, but they remains observations... It makes little sense to speak about them...

HTH
Chris

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