Also, Oracle software is tested on 8K database so a 16K specific bug may
remain undiscovered until the lucky DBA in search of an adventure
discovers it on his production database. Then the life will turn into
the support nightmare on the elm street. However, if you have a
non-Exadata box with huge amount of memory and fast flash storage that
can read more than 1MB in one operation, then I would consider 16K.
On 6/10/20 8:37 PM, Herring, Dave (Redacted sender HerringD for DMARC)
wrote:
--
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@xxxxxxxxxxxxx <oracle-l-bounce@xxxxxxxxxxxxx> *On Behalf Of *Neil Chandler
*Sent:* Wednesday, June 10, 2020 11:09 AM
*To:* oracle-l@xxxxxxxxxxxxx; oralrnr@xxxxxxxxx
*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@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
