Re: Block size qn

In the absence of simultaneous updates and inserts, for a typical DW type database with nightly loads and queries by day, it looks like16K is probably a better choice.
Esp, if there are cases where the rows do not fit in one block, this is very useful. Thank you

On Fri, Jun 12, 2020 at 6:50 PM Orlando L <oralrnr_at_gmail.com> wrote:

> *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