We have a medium db (about 80TB) sitting on a 4-node X-8 system. Oracle reviewed performance as part of our move from X-4 system and one of their recommendations was to move to an 8K block size (currently it's 16K). Their reasoning? When you use a non-8K block size on Exadata you bypass various optimizations that Exadata could perform. Unfortunately they didn't elaborate on what those optimizations are related to 8K block size and we didn't have a downtime window that would allow us to change the block size. Still, if you're on Exadata it's something to factor.
Regards,
Dave
From: oracle-l-bounce_at_freelists.org <oracle-l-bounce_at_freelists.org> On Behalf Of Neil Chandler
Sent: Wednesday, June 10, 2020 11:09 AM
To: oracle-l_at_freelists.org; oralrnr_at_gmail.com
Subject: Re: Block size qn
CAUTION: This email originated from outside of D&B. Please do not click links or open attachments unless you recognize the sender and know the content is safe.
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_at_freelists.org <oracle-l-bounce_at_freelists.org> on behalf of Orlando L <oralrnr_at_gmail.com>
Sent: 10 June 2020 16:14
To: oracle-l_at_freelists.org <oracle-l_at_freelists.org>
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
