Orlando,
The contention relates to DML (not queries) on the blocks so it would depend on
how data is populated.
* If it's bulk loads and manipulations there is less opportunity for a
problem.
* If it's transactional (OLTP) with simultaneous inserts and updates from
many processes, then the increased number of rows per blocks comes into play.
The more rows per leaf block, the more opportunity for contention in this
scenario.
regards
Neil.
________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx <oracle-l-bounce@xxxxxxxxxxxxx> on behalf
of Orlando L <oralrnr@xxxxxxxxx>
Sent: 11 June 2020 18:29
To: jlewisoracle@xxxxxxxxx <jlewisoracle@xxxxxxxxx>
Cc: Mladen Gogala <gogala.mladen@xxxxxxxxx>; Oracle L <oracle-l@xxxxxxxxxxxxx>
Subject: Re: Block size qn
Thank you all. I am not talking about mixing 8K and 16K. It is either one or
the other. Looks like we have less to worry about row chaining with 16K where
data for one row cannot fit in a 8K block. I read the link provided by
Jonathan. It looks like 16K are tested thoroughly by Oracle corp ("thoroughly
tested just like 8k blocks"). No reason not to consider 16K if that is going to
be the only size used in the DB. It will be a typical DW with loads in the
nights and queries during days.
Neil, can you please explain "Larger block sizes will increase index block
contention". Why would there be contention for an index block? If the data in
the index blocks are accessed frequently, wouldn't the chances of the index
blocks being cached go up, hence producing better results. I think the
assumption here is that the index blocks can contain way more 'rows' than data
blocks. If the individual rows in the index blocks are accessed simultaneously
and if they are in memory, how would there be contention? Is it because in DW
the queries are typically performing range scans and multiple queries could be
doing range scans simultaneously on frequently accessed blocks. If that is the
case, if there are 2 queries accessing the same block and if they are both
readers, can they not read the block simultaneously? Am I understanding this
correctly
Thanks for your time.
Orlando
On Thu, Jun 11, 2020 at 4:05 AM Jonathan Lewis
<jlewisoracle@xxxxxxxxx<mailto:jlewisoracle@xxxxxxxxx>> wrote:
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@xxxxxxxxx<mailto:gogala.mladen@xxxxxxxxx>> 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@xxxxxxxxxxxxx<mailto:oracle-l-bounce@xxxxxxxxxxxxx>
<oracle-l-bounce@xxxxxxxxxxxxx><mailto:oracle-l-bounce@xxxxxxxxxxxxx> On Behalf
Of Neil Chandler
Sent: Wednesday, June 10, 2020 11:09 AM
To: oracle-l@xxxxxxxxxxxxx<mailto:oracle-l@xxxxxxxxxxxxx>;
oralrnr@xxxxxxxxx<mailto: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<mailto:oracle-l-bounce@xxxxxxxxxxxxx>
<oracle-l-bounce@xxxxxxxxxxxxx><mailto:oracle-l-bounce@xxxxxxxxxxxxx> on behalf
of Orlando L <oralrnr@xxxxxxxxx><mailto:oralrnr@xxxxxxxxx>
Sent: 10 June 2020 16:14
To: oracle-l@xxxxxxxxxxxxx<mailto:oracle-l@xxxxxxxxxxxxx>
<oracle-l@xxxxxxxxxxxxx><mailto: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
--
Mladen Gogala
Database Consultant
Tel: (347) 321-1217
