Re: db_file_multiblock_read_count and 10g

1. First of all, I'm not telling that OP should change the hidden
parameters.
I'm just telling about *the danger of unexpected plan change*.

The important thing is that there are 3 mbrc(s) as of Oracle 10gR2.
- _db_file_exec_read_count (for execution)
- _db_file_optimizer_read_count (for optimization)
- and mbrc in system stat (for optimization)


2. This autotuned(whatever we call, default value determined by Oracle?
autotuned? blah blah) MBRC feature is supported from *10gR2 version*.
Actually, above 2 mbrc hidden parameters came with ths new feature.

We don't need to use this new feature just because Oracle implemented it.
All we should do is to compare the performance of autotuned mrbc and
manually tuned mbrc.


================================
Dion Cho - Oracle Performance Storyteller

http://dioncho.wordpress.com (english)
http://ukja.tistory.com (korean)
================================


2009/1/25 Nuno Souto <dbvision@xxxxxxxxxxxx>

> 조동욱 wrote,on my timestamp of 25/01/2009 1:44 AM:
>
>> This is called autotuned MBRC - literally, Oracle autotunes the value of
>> MBRC.
>>
>
>
> I don't think this is the case in 10g. Maybe in 11, but not 10gr2.
> See this:
>
> http://download.oracle.com/docs/cd/B19306_01/server.102/b14237/initparams047.htm#CHDFAFHE
> There is a world of difference between a "default value" and "auto-tune".
> Nowhere in that doco is it said it is auto-tuned.  I do recall reading
> something from Jonathan about it being so in 11g but I won't be using 11g
> anytime soon so I can't say for sure.
>
>
>  The best practice is
>>
>>    * Set system stat's MRBC to your current
>>      db_file_multiblock_read_count value, like
>>      dbms_stats.set_system_stats('mbrc', 64). This would gurantee that
>>      your execution plans would not be changed unexpectedly by changed
>>      MBRC value.
>>    * Then use autotuned MBRC
>>
>
> Another thing: if one has the db files in a SAN using RAID10, then perhaps
> it's a good idea to bypass all this auto-magical auto-tuned lowest common
> denominator stuff and set dbfmrc to a value close to the stripe size.
> That is usually, but not always, 64KB.  I don't know about you but I still
> have to see Oracle magically figure out what the stripe size is in a SAN all
> the way back from the OS...
>
>
> --
> Cheers
> Nuno Souto
> in sunny Sydney, Australia
> dbvision@xxxxxxxxxxxx
>
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>


-- 
==================================================
Oracle Performance Storyteller
조 동욱(Dion Cho)
EXEM. 02) 6203-6300 ukja@xxxxxxxxx, ukja.dion@xxxxxxxxx

http://ukja.tistory.com
http://dioncho.wordpress.com
http://wiki.ex-em.com/index.php/performance_in_depth
http://wiki.ex-em.com/index.php/optimizing_oracle_optimizer
===================================================

Other related posts: