Re: Bigger block sizes

From: Andrew Kerber <andrew.kerber_at_gmail.com>
Date: Thu, 1 Oct 2015 11:26:25 -0500
Message-ID: <CAJvnOJbSSKci-1GkY62cOqmkzbUL6GT14X3TiZPyb_rQoZfdpw_at_mail.gmail.com>

Tim can always be counted on as the voice of rationality. Here is the link I was referencing by the way. There are several discussions on the subject on asktom, and as I recall Steve Karam ran into an Oracle bug with i/o on some block sizes a few years ago. I think it has since been fixed:

https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:5942798800346009065

On Thu, Oct 1, 2015 at 11:20 AM, Tim Gorman <tim_at_evdbt.com> wrote:

> Franck's post is great, but doesn't cover some other factors that impact
> the decision.
>
> Consider a RAC database with a lot of cross-instance communication.
> Cross-instance communication is clearly dependent on how the application
> was designed, configured, and deployed. TCP jumbo frames are recommended
> for the private interconnect in RAC because it raises the maximum TCP
> packet size from the default of 1500 to 9000 (or a little less considering
> packet headers). When DB_BLOCK_SIZE is 8192, then clearly MTU=9000 is
> optimal.
>
> Now just because the DB_BLOCK_SIZE changed to 16384 or 32768 doesn't mean
> that MTU=9000 is not a good thing, just not as good as before.
>
> Is MTU <= 9000 a problem? Increasing MTU size doesn't increase native
> network throughput, but it does cut down on the processing by the server to
> packet-ize and un-packet-ize, thus it does decrease CPU utilization.
> Having DB_BLOCK_SIZE=8192 makes Oracle block slide nicely into TCP packets
> with a minimum of effort. Having to packet-ize and un-packet-ize is only a
> matter of expending more CPU.
>
> Is CPU utilization a problem? Only if your servers are maxed out for
> CPU. If they aren't, then no problem. If they are, then you have a
> serious problem.
>
> The upshot is: are you prepared to test all these factors (and others not
> mentioned)?
>
> If the answer is yes, then have at it, and good luck.
>
> If you're just looking for easy optimizations, then this isn't it. There
> are far more effective optimizations to consider.
>
> My US$0.02...
>
>
>
>
>
> On 10/1/15 10:02, Stefan Koehler wrote:
>
>> Hi Orlando,
>> it depends (as always).
>>
>> Franck Pachot has written a great blog post with several demos about this
>> topic: http://blog.dbi-services.com/do-the-block-size-matter/
>> Best Regards
>> Stefan Koehler
>>
>> Freelance Oracle performance consultant and researcher
>> Homepage: http://www.soocs.de
>> Twitter: _at_OracleSK
>>
>>
>>> Orlando L <oralrnr_at_gmail.com> hat am 30. September 2015 um 23:29
>>> geschrieben:
>>>
>>> List,
>>> Does anyone in the list use non default blocksize of greater than
>>> 8K for your oracle DBs; if so, is it for warehousing/OLAP type applications?
>>> What advantages do you get with them; any disadvantage.
>>> Orlando.
>>>
>> --
>> http://www.freelists.org/webpage/oracle-l
>>
>>
>>
>>
>>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

-- 
Andrew W. Kerber

'If at first you dont succeed, dont take up skydiving.'

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

Received on Thu Oct 01 2015 - 18:26:25 CEST