Re: db_file_multiblock_read_count and 10g
Date: Sun, 25 Jan 2009 18:13:11 +0900
Message-ID: <43c2e3d60901250113l730335a4g807aaadf5e0a6c58_at_mail.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.*
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 11.1.0.6), _db_file_optimizer_read_count was always 8.
Dion Cho - Oracle Performance Storyteller
http://dioncho.wordpress.com (english)
http://ukja.tistory.com (korean)
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
>>
>> http://download.oracle.com/docs/cd/B19306_01/server.102/b14214/chapter1.htm#FEATURENO05506
>>
>> 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:
>> > Á¶µ¿¿í 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.
>>
>> --
>> Regards,
>> Greg Rahn
>> http://structureddata.org
>> --
>> http://www.freelists.org/webpage/oracle-l
>>
>>
>>
>
>
> --
> Best Regards,
>
> Syed Jaffar Hussain
> Oracle Certified Master (10g)
> http://www.oracle.com/technology/ocm/shussain.html
> Oracle ACE
> http://apex.oracle.com/pls/otn/f?p=19297:4:1579866181463918::NO:4:P4_ID:126
> OCP 8i,9i & 10g DBA
> RAC Certified Expert
> Official Oracle RAC SIG Representative for Saudi Arabian region (
> http://www.oracleracsig.org/)
> I blog at
> http://jaffardba.blogspot.com/
> --------------------
> "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