Re: db_file_multiblock_read_count and 10g
From: Nuno Souto <dbvision_at_iinet.net.au>
Date: Sun, 25 Jan 2009 11:18:47 +1100
Message-ID: <497BAFE7.5020204_at_iinet.net.au>
조동욱 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.
Date: Sun, 25 Jan 2009 11:18:47 +1100
Message-ID: <497BAFE7.5020204_at_iinet.net.au>
조동욱 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_at_iinet.net.au -- http://www.freelists.org/webpage/oracle-lReceived on Sat Jan 24 2009 - 18:18:47 CST