Re: Block size qn

Hi,

For information, my current client has been using a 32k block size in an Exadata X6 data warehouse database and has encountered none issue for 10 years regarding that setting. Of course, we never update rows, and delete statements are very rare. We mainly use direct path inserts and truncate partitions.

Regards,
Ahmed

Le sam. 13 juin 2020 à 01:57, Orlando L <oralrnr_at_gmail.com> a écrit :

> 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