General rule of thumb: stick with 8k unless you have a very good reason, and
have tested and proven that 16k provides tangible benefits over 8k.
Larger block sizes will increase index block contention but may reduce block
chaining and is generally better for LOBs.
The overriding reason not to use 32K block sizes: Oracle standard regression
tests do not test 32K block sizes. It mostly tests 8K with some 16K testing.
For this reason alone, I'd avoid 32k block sizes - they aren't testing their
code against it.
You probably shouldn't mix block sizes in the same database.
Of course, your milage may vary. Test test test. Then stick with 8k 🙂
Neil Chandler
Database Guy
________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx <oracle-l-bounce@xxxxxxxxxxxxx> on behalf
of Orlando L <oralrnr@xxxxxxxxx>
Sent: 10 June 2020 16:14
To: oracle-l@xxxxxxxxxxxxx <oracle-l@xxxxxxxxxxxxx>
Subject: Block size qn
Hi,
We are in planning stages for a big DW. Do any of the listers have a block size
bigger than the default 8k in their site? Is there a need for anything like
that? Any advantages or pitfalls?
Thanks,
Orlando