Re: db_file_multiblock_read_count and 10g
Date: Sun, 25 Jan 2009 18:13:11 +0900
*What an odd behavior I have come across when the the value is left for auto tune (on 10gR2), I have observed that the value goes to a higher value and it was causing optimize to favor FTS.*
In your case, which value are you mentioning? _db_file_exec_read_count vs. _db_file_optimizer_read_count?
Oracle does not auto-tune _db_file_optimizer_read_count. Auto-tuned MBRC is only applied to _db_file_exec_read_count.
On my systems(10.2.0.1 and 188.8.131.52), _db_file_optimizer_read_count was always 8.
Dion Cho - Oracle Performance Storyteller
2009/1/25 Syed Jaffar Hussain <sjaffarhussain_at_gmail.com>
> What an odd behavior I have come across when the the value is left for auto
> tune (on 10gR2), I have observed that the value goes to a higher value and
> it was causing optimize to favor FTS.
> 2009/1/25 Greg Rahn <greg_at_structureddata.org>
> 10gR2 introduced Self-Tuning Multiblock Read Count
>> The basic premise is to allow large I/Os for MBRC operations w/o
>> giving the Optimizer too much preference to FTS. By default it uses a
>> MBRC of 8 for costing, or the system stats MBRC if it is set.
>> On Sat, Jan 24, 2009 at 4:18 PM, Nuno Souto <dbvision_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
>> >> MBRC.
>> > I don't think this is the case in 10g. Maybe in 11, but not 10gr2.
>> > See this:
>> > There is a world of difference between a "default value" and
>> > 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
>> > anytime soon so I can't say for sure.
>> Greg Rahn
> Best Regards,
> Syed Jaffar Hussain
> Oracle Certified Master (10g)
> Oracle ACE
> OCP 8i,9i & 10g DBA
> RAC Certified Expert
> Official Oracle RAC SIG Representative for Saudi Arabian region (
> I blog at
> "Winners don't do different things. They do things differently."
-- ================================================== Oracle Performance Storyteller 조 동욱(Dion Cho) EXEM. 02) 6203-6300 ukja_at_ex-em.com, ukja.dion_at_gmail.com 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 =================================================== -- http://www.freelists.org/webpage/oracle-lReceived on Sun Jan 25 2009 - 03:13:11 CST