Hi all,
We were having high ITL waits and high number of deadlocks (due to the hanging transactions waiting on the ITL on the data block) on a table and index and part of the fix was to increase the INITRANS on the table and on the index.
INITRANS is a physical attribute that determines the initial number of concurrent transaction entries allocated within each data block for a given table/index/cluster. Every transaction that updates a block has to acquire an Interested Transaction List (ITL) slot, in order to store the transaction id, rollback information and ultimately lock the necessary row/index entry within the block.
For an existing object it needs to be rebuild as INITRANS is a physical attribute on the Oracle datablock. So if you change on a table and you want it to take effect for the current data you need to move the table or one partition for example.
If you are doing this type of change on a partitioned index you need to change the index default attribute as well rebuild the index partition or the entire index.
But one thing caught my eye while working on this as after setting the new INITRANS default attribute and rebuilt the index online it did not changed the INITRANS values.
See below the execution on a test system
SQL> select INDEX_OWNER,index_name,PARTITION_NAME,HIGH_VALUE,INI_TRANS from dba_ind_partitions where index_name ='INDEX1' and INDEX_OWNER='USER1';
INDEX_OWNE INDEX_NAME PARTITION_NAME HIGH_VALUE INI_TRANS
---------- ------------------------- --------------- -------------------------------------------------------------------------------- ----------
USER1 INDEX1 P201606 TO_DATE(' 2016-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA 2
USER1 INDEX1 SYS_P924430 TO_DATE(' 2018-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA 2
USER1 INDEX1 SYS_P926915 TO_DATE(' 2018-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA 2
USER1 INDEX1 SYS_P929165 TO_DATE(' 2018-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA 2
USER1 INDEX1 SYS_P931615 TO_DATE(' 2019-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA 2
SQL> ALTER INDEX USER1.INDEX1 MODIFY DEFAULT ATTRIBUTES INITRANS 4;
Index altered.
SQL> select INDEX_OWNER,index_name,PARTITION_NAME,HIGH_VALUE,INI_TRANS from dba_ind_partitions where index_name ='INDEX1' and INDEX_OWNER='USER1';
INDEX_OWNE INDEX_NAME PARTITION_NAME HIGH_VALUE INI_TRANS
---------- ------------------------- --------------- -------------------------------------------------------------------------------- ----------
USER1 INDEX1 P201606 TO_DATE(' 2016-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA 2
USER1 INDEX1 SYS_P924430 TO_DATE(' 2018-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA 2
USER1 INDEX1 SYS_P926915 TO_DATE(' 2018-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA 2
USER1 INDEX1 SYS_P929165 TO_DATE(' 2018-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA 2
USER1 INDEX1 SYS_P931615 TO_DATE(' 2019-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA 2
SQL> alter index USER1.INDEX1 rebuild partition SYS_P931615 online;
Index altered.
SQL> select INDEX_OWNER,index_name,PARTITION_NAME,HIGH_VALUE,INI_TRANS from dba_ind_partitions where index_name ='INDEX1' and INDEX_OWNER='USER1';
INDEX_OWNE INDEX_NAME PARTITION_NAME HIGH_VALUE INI_TRANS
---------- ------------------------- --------------- -------------------------------------------------------------------------------- ----------
USER1 INDEX1 P201606 TO_DATE(' 2016-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA 2
USER1 INDEX1 SYS_P924430 TO_DATE(' 2018-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA 2
USER1 INDEX1 SYS_P926915 TO_DATE(' 2018-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA 2
USER1 INDEX1 SYS_P929165 TO_DATE(' 2018-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA 2
USER1 INDEX1 SYS_P931615 TO_DATE(' 2019-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA 2
It changed only when I specified the new INITRANS in the index rebuild clause:
SQL> alter index USER1.INDEX1 rebuild partition SYS_P931615 INITRANS 4 online;
Index altered.
SQL> select INDEX_OWNER,index_name,PARTITION_NAME,HIGH_VALUE,INI_TRANS from dba_ind_partitions where index_name ='INDEX1' and INDEX_OWNER='USER1';
INDEX_OWNE INDEX_NAME PARTITION_NAME HIGH_VALUE INI_TRANS
---------- ------------------------- --------------- -------------------------------------------------------------------------------- ----------
USER1 INDEX1 P201606 TO_DATE(' 2016-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA 2
USER1 INDEX1 SYS_P924430 TO_DATE(' 2018-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA 2
USER1 INDEX1 SYS_P926915 TO_DATE(' 2018-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA 2
USER1 INDEX1 SYS_P929165 TO_DATE(' 2018-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA 2
USER1 INDEX1 SYS_P931615 TO_DATE(' 2019-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA 4
Interesting, right?
Hope it helps,
Elisson Almeida