Re: question on initrans

Thank you very much Gopal and Mark. Your explanation really helped me to understand it better. Thank you.

I will try to dig more into the buffer busy wait part and try to see pctfree and minimize_records_per_block options. Btw From the current_obj# in v$actie_session_history i see the top contributor is pointing to a non partitioned table(In which single row INSERT is happening from multiple session) followed by an index partition(index is on a sequence generated column) and here UPDATE happening on that indexed column.

Regards
Pap

On Wed, May 12, 2021 at 9:01 PM K Gopalakrishnan <kaygopal_at_gmail.com> wrote:

> Pap-
>
> ASM manages the storage at the host level and ASSM manages the storage at
> Segment level.
> Within the segments, you have extents and ASSM manages extents
> (allocation, size etc)
> ITLs manage block level concurrency issues.
>
> On the other hand, Freelists maintain the list of blocks which are
> eligible for INSERTs in non ASSM managed extents.
> With ASSM, there are no freelists as the insert eligible blocks are
> managed by bitmap blocks in segment headers.
> There are multiple levels of bitmap blocks (Level 1 - Leaf BMB/ Level 2 -
> Branch BMB and Level 3 (root BMB)) in a tree foramt manages the space
> inside the segments.
> Most of these things are automated and seldom require any changes. You can
> still set PCTFREE here as that is honored.
> PCTUSED used to decide when to link/unlink the blocks from the freelist
> chain. This is replaced by different groupings in ASSM.
>
> BTW you mention you are encountering Buffer Busy Waits and this could be
> totally a different issue altogether.
> In this case, you have to reduce the block level concurrency - (rather
> than improving it with ITL fixes).
> You can either increase the PCTFREE to higher values or use Jonathan
> Lewis's trick (minimize_records_per_block).
>
> -Gopal
>
>
> On Tue, May 11, 2021 at 10:41 PM 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