Re: Block size qn

A critical reference document about blocksizes is this one from Roger McNical (Mr. Tablescan):
https://blogs.oracle.com/smartscan-deep-dive/random-thoughts-on-block-sizes

Read it all, but here's an important pair of myth-buster points:

1. A quick scan of the data layer regression tests showed a very large number running on 16k blocks
2. Oracle typically runs it DW stress tests on 16k blocks

From my own experience:
a) There are a couple of boundary cases where a 16KB or 32KB block size has a negative effect (typically due to bugs) b) Using multiple block sizes in a single database is probably sub-optimal - but it is possible to find special cases. c) For RAC systems a block size that fits a single message on the interconnect is a nice idea
d) Creating realistic tests of the effects of different block sizes is very hard and very time-consuming

Regards
Jonathan Lewis

On Thu, Jun 11, 2020 at 4:24 AM Mladen Gogala <gogala.mladen_at_gmail.com> wrote:

> 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_at_freelists.org <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>
> <oracle-l-bounce_at_freelists.org> on behalf of Orlando L <oralrnr_at_gmail.com>
> <oralrnr_at_gmail.com>
> *Sent:* 10 June 2020 16:14
> *To:* oracle-l_at_freelists.org <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
>
> --
> Mladen Gogala
> Database Consultant
> Tel: (347) 321-1217
>
>

--
http://www.freelists.org/webpage/oracle-l