Re: question on initrans

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