Choosing the right blocksize for Oracle Database tablespaces with lobs

Posted on

Question :

We are currently switching from an older character set to Unicode using impdp/expdp on Oracle Database 19c. We had to changed several data types. We changed tables with LOBs from basicfile to securefile and found that the tablespace holding this lobs only uses an 8K blocksize. The table containes several TBs with pdfs around 2-3mb. Does it makes sense to increase the Blocksize of the tablespace holding the LOBs from 8k to 32k?

Answer :

LOBs are managed in chunks (groups of blocks), not blocks, so there’s not really anything to gain with this. Oracle assumes that an 8k block size is “universally good” for everything. By increasing the block size, you could potentially wind up wasting storage space that can’t be efficiently allocated for smaller files because the chunk size is too large.

See here for a running discussion:

Final analysis:

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.

Leave a Reply

Your email address will not be published. Required fields are marked *