Re: Bigger block sizes

That thread (re: the bug) has a tangled mess of comments at the bottom best left archived. 

But yes, IIRC the client requested we try a 16kb block size and it drastically improved performance; however, this wasn’t due to the blocksize per se, it was due to a bug in ASSM that affected 9i-11g at the time (not sure where it is now) when non-default block sizes were used in conjunction with particular PCTFREE settings. In the end the customer declared it a “fix” because of the time it would take to resolve the bug another way. I’ve not seen a definitive non-bug-induced case where changing the block size for performance provides a guaranteed tangible benefit, particularly one that would make the risk/unknowns of a non-default block worthwhile.

Tim said it best: are you prepared to test all these factors?

Steve Karam
OracleAlchemist.com
On October 1, 2015 at 12:27:10 PM, Andrew Kerber (andrew.kerber_at_gmail.com) wrote:

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

--

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:34:49 CEST