Re: question on initrans

  And yes, we have the block size kept as standard 8K.

On Wed, May 12, 2021 at 9:10 AM Pap <oracle.developer35_at_gmail.com> wrote:

> Thank you so much for this detailed explanation.
>
> If I get it correctly, it's the ASM tablespace(which we do have) manages
> the dynamic increase and decrease of ITL or initrans based on concurrency
> so we don't' need to set them explicitly. Also it should be looked into
> only if by we see the ITL related waits. Correct if my understanding is
> correct. As you mentioned in case of high concurrency we may need to
> increase pctfree to higher value, will that not automatically managed by
> ASM and also in such cases do we see any other waits or same ITL waits will
> be seen in AWR?
>
> Is freelists also managed dynamically as above and no need manually to be
> set to higher on a ASM? (Note-Actually we are encountering buffer busy
> waits during high activity period and the object it's pointing to is table
> followed by index.)
>
>
> Regards
> Pap
>
> On Wed, 12 May 2021, 8:20 am K Gopalakrishnan, <kaygopal_at_gmail.com> wrote:
>
>> Pap-
>>
>> The parameter INITRANS - defines number of ITL (Interested Transaction
>> Lists) slots _initially_ created during new blocks allocation to the
>> segment.
>> Any transaction that is interested in conducting DMLs on that block,
>> should fill in some details about that transaction.
>> This includes setting the Transaction ID, Undo Block Address, Lock Byte ,
>> Cleanout System Change Number and Free Space Credit.
>> The row level locking in Oracle is implemented with the help of these ITL
>> information as well.
>>
>> In other words, treat ITL like your parking space in the office space.
>> Anyone entering the office must park their car in the parking space
>> allotted.
>> You can park your car in the allotted space or any free space in that
>> building. Alloted parking spaces are fixed ITLs during the creation of the
>> block.
>> If allocated parking space is full, you park in the free space in the
>> building. Once you use this free space, this space will never return data
>> storage, only for ITLs.
>> This defaults to 2 per table and 3 per index IIRC, but DBA_TABLES still
>> show the default as 1 per table and 2 per index.
>> You can quickly validate this by simple block dumps.
>>
>> Note that the ITLs can be created when there is enough space (~24 bytes
>> per ITL) in the variable header space in the data block.
>> So when there is a demand for additional transaction slots, oracle
>> automatically creates ITL slots subject to free space in the block.
>> If there is no space in the block , you might see contention for ITLs and
>> you will see ITL waits in the session_wait views.
>>
>> There are some internal validations in oracle to limit the variable part
>> of the block header that can not exceed 50% of the block size.
>> i.e you can not create 50 ITLs if the block size is 2K. (50 ITLs require
>> 50x24=1200+ bytes which is clearly over 50% of the block size).
>> There are many other checks similar to these as well to stop the abuse :)
>>
>> Also the requirement of such high ITL depends on the number of active
>> rows per block.
>> If the block itself has less than 50 rows, you would neer need 50 ITLs
>> for that block.
>> Similar to parking slot analogy, you would not need more parking spots
>> than the number of residents.
>> Readers (aka visitors) do not need any ITL entries to record their
>> transactions.
>>
>> You do not need to manually set the ITLs, as ITLs will be created on the
>> need basis.
>> If there is an issue with the ITLs , you will clearly see this in the
>> session_waits.
>> Also segment_stats has specific metrics to show the objects with ITL
>> contention.
>> If you are not seeing any of this in the DB, this recommendation from
>> oracle is totally irrelevant.
>> So 50 ITLs (per table & 100 ITLs on their indexes) seems such a waste of
>> block space.
>>
>> There are much better mechanisms to handle this. If you suspect such a
>> high rate of concurrency,
>> please set the PCTFREE to a higher number (say 15%) so that additional
>> ITLs can be created when required.
>> The space reserved with PCTFREE can not be used for INSERTS. They are
>> used for ITL expansion and updates.
>>
>> -Gopal
>>
>>
>>
>> On Tue, May 11, 2021 at 11:47 AM Pap <oracle.developer35_at_gmail.com>
>> wrote:
>>
>>> This database was recently moved from HP to exadata X7 and the DB
>>> version also migrated from 11.2.0.4 to 19.9.0.0.0. It's an OLTP database.
>>> In the past we saw some contention and got a recommendation from Oracle to
>>> set INITRANS value of certain objects(both table and indexes) to as high
>>> as 50. And now the team is thinking to set the initrans value to ~10 for
>>> almost all the objects(~around ~200+ objects). Want to understand , what is
>>> the negative side of setting it to this non default value considering this
>>> is an OLTP database exposed to many concurrent DML/query and we
>>> encounter "buffer busy waits" many times?
>>>
>>> Regards
>>> Pap
>>>
>>

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