Re: Block size qn

*THAT* clears up my confusion very well. Thanks a lot Mark.

On Fri, Jun 12, 2020 at 3:02 PM Mark J. Bobak <mark_at_bobak.net> wrote:

> Hi Orlando,
>
> In terms of block-level contention, we are referring the the Interested
> Transaction List (ITL) and the number of ITL entries per block. The
> default for ITL entries is 1 for table blocks and 2 for index blocks. You
> can increase the INITRANS value, but it will cost you 24 bytes per ITL
> entry. So, if you have a table that has 10 rows per block, but you only
> have 3 ITL entries per block, then you could end up with block-level
> contention due to the shortage of ITL entries. Note that a row that is
> being updated points to an ITL entry, and that ITL entry in turn points to
> the transaction's UNDO. So, the larger the number of different
> transactions that are interested in the data in a particular block, the
> higher the chance of block-level contention.
>
> Note that even if you're at the default of 1 ITL entry for a given table,
> Oracle will automatically grow the ITL (dynamically add entries) as needed,
> as long as there is free space available in the block. So, if you have
> plenty of free space in the block, no worries. But ,if you have blocks
> with tightly packed rows and didn't plan ahead with additional ITL slots,
> you could end up with ITL waits. (Note that on table blocks, only update
> and deletes suffer from ITL waits, not inserts, but this is not true for
> indexes. An insert on an index must go to a specific block, so, if there's
> an ITL shortage, indexes are always subject to ITL waits.
>
> -Mark
>
> On Fri, Jun 12, 2020 at 3:18 PM Orlando L <oralrnr_at_gmail.com> wrote:
>
>> Thanks again.
>>
>> "If it's transactional (OLTP) with simultaneous inserts and updates from
>> many processes, then the increased number of rows per blocks comes into
>> play." If we are having, say 10 insert/updates happening to one 8K block,
>> then with a 16K block, there could be 20 insert/updates. If that is the
>> case, what is 'contention'? Underneath an oracle block are multiple OS
>> blocks (512b or 1K blocksize?). Plus locking also should not be an issue
>> since locks are at the row level. Trying to understand, thank you for your
>> time in advance.
>>
>>
>>
>> On Thu, Jun 11, 2020 at 12:57 PM Neil Chandler <neil_chandler_at_hotmail.com>
>> wrote:
>>
>>> 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_at_freelists.org <oracle-l-bounce_at_freelists.org>
>>> on behalf of Orlando L <oralrnr_at_gmail.com>
>>> *Sent:* 11 June 2020 18:29
>>> *To:* jlewisoracle_at_gmail.com <jlewisoracle_at_gmail.com>
>>> *Cc:* Mladen Gogala <gogala.mladen_at_gmail.com>; Oracle L <
>>> oracle-l_at_freelists.org>
>>> *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_at_gmail.com>
>>> 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_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