Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question.

Asked: January 17, 2013 - 11:52 pm UTC

Last updated: October 01, 2018 - 2:36 am UTC

Version: 10.2

Viewed 10K+ times! This question is

You Asked

Hi Tom,

I would be very grateful if you could share your thoughts on Oracle block size.

"rule of thumb" is Oracle Database block sizes (2 KB or 4 KB) for online transaction processing (OLTP) or mixed workload environments and larger block sizes (8 KB, 16 KB, or 32 KB) for decision support system (DSS) workload environments

Would you have any recent tests/insights you can share and especially under what circumstances have you found using mixed block sizes to be appropriate

thanks Doug

and Tom said...

I would say that rule of thumb is a really old topic (rot is rot ;)

I would go with 8k for almost everything - using 16/32k only in a warehouse environment - but maybe not even there.

8k is universally "good", it is what everything we build was built assuming would be the case. It is the default and defaults are the way to go unless you have a really compelling reason to not go that way.

for example - you can get a little be better compression on a 16k block than an 8k block and there will be less block overhead as well. So, that might be compelling enough to go 16k for your warehouse if minimizing space was one of your main goals.

for right now, in 2013, I see no real reason to deviate from 8k in general.


Rating

  (9 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Alexander, January 18, 2013 - 1:31 pm UTC

Why does it not matter that the Oracle block size match up with the default Unix (Linux at least) block size? I would think it would be beneficial to have them line up from an OS memory management point of view. I don't actually know what I'm talking about but it just sounds right and I wonder what the explanation is whether it is true or not.
Tom Kyte
January 18, 2013 - 1:57 pm UTC

linux block sizes are typically 1k (sometimes 2k, 4k), so they all line with up our block sizes nicely.

Alexander, January 18, 2013 - 2:25 pm UTC

I thought Linux (64 bit) was typically 4k. Oracle being 8k, wasn't sure what the relationship was there.

This reminds me of something else I wanted to ask on a related subject, have you ever in your life went to using hugepages to solve or improve any Oracle performance problems?
Tom Kyte
January 18, 2013 - 3:59 pm UTC

they need to be multiples - OS block sizes are pretty much going to be less than the database block size.


I know of customers that have used hugepages yes. It can be beneficial.


http://kevinclosson.wordpress.com/2009/07/28/quantifying-hugepages-memory-savings-with-oracle-database-11g/

http://kevinclosson.wordpress.com/2010/09/28/configuring-linux-hugepages-for-oracle-database-is-just-too-difficult-part-i/
http://kevinclosson.wordpress.com/2010/10/21/configuring-linux-hugepages-for-oracle-database-is-just-too-difficult-isn%E2%80%99t-it-part-%E2%80%93-ii/
http://kevinclosson.wordpress.com/2010/11/18/configuring-linux-hugepages-for-oracle-database-is-just-too-difficult-isn%e2%80%99t-it-part-%e2%80%93-iii-do-you-really-want-to-configure-the-absolute-minimum-hugepages/

Thankyou

Doug, January 20, 2013 - 6:10 pm UTC

Thanks Tom

with securefile lobs?

Charles, January 29, 2013 - 6:25 am UTC

Hi Tom,
Would you take the same stance of universal 8k block size even when using securefile lobs in their own tablespace? Have you seen any benefit from a 32k block size for lob tablespace and 8k block size for tablespace holding the structured data?
thanks for your opinion on this.

Charles
Tom Kyte
January 31, 2013 - 9:19 am UTC

there could be some minor efficiencies to be seen by aligning the block size with the chunk size - but not really. LOBS are managed in chunks already, not in single blocks necessarily.


I have not directly measured this for a few releases, it would be something to benchmark and evaluate. but I'm leaning towards "no"

ik

kylsij, February 17, 2013 - 4:34 am UTC

ik

A reader, July 16, 2013 - 11:17 am UTC


db block size for large ETL/DW environment

Ravi B, February 03, 2014 - 9:50 pm UTC

Hi Tom,

Could you please let us know if the following explanation justifying large db block size is worth considering?

http://www.ixora.com.au/tips/block_size.htm

16K blocksize

Roger MacNicol, November 09, 2017 - 1:58 pm UTC

The comment about Oracle not testing 16k blocks is just wrong. A quick scan of the regression tests shows a very large number running on 16k blocks and Oracle's DW stress tests run on 16k blocks.

That said, worrying about block size is like fiddling while Rome burns - many (most?) apps have low hanging fruit which will give a better ROI on your time than tuning the block size.

8k is still best block size to be using.
Chris Saxon
November 09, 2017 - 2:29 pm UTC

Thanks for letting us know Roger.

32K block size regression testing

Naresh, March 20, 2018 - 2:31 pm UTC

hi,

Thanks for the info on 16K regression tests.

Do you also have the same level of regression tests done for 32K block size?

Connor McDonald
March 21, 2018 - 11:14 am UTC

That said, worrying about block size is like fiddling while Rome burns - many (most?) apps have low hanging fruit which will give a better ROI on your time than tuning the block size.

8k is still best block size to be using.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Administration

Need more information on Administration? Check out the Administrators guide for the Oracle Database