Note on table ITL (enq: TX - allocate ITL entry)

Oracle keeps track of which rows are locked by which transaction in an area at the top of each data block known as the 'interested transaction list'.

Overview of Data Blocks

The INITRANS segment parameter guaranteed the ITL allocation at block allocation while MAXTRANS specify maximum allow depending on free space available in the block.

ITL become a bottleneck when there are heavy parallel DML on segments with scarcity of ITL in associated data blocks. “enq: TX - allocate ITL entry” events is an indicator of ITL contention.

Here I am going to create a test case to simulate ITL contention using a single 8k blocka and how to avoid ITL contention. Tested on Oracle version 18.1/Red Hat 7.4. same principle applies to older versions

CREATE TABLESPACE ITL_8KB datafile SIZE 1M    EXTENT MANAGEMENT LOCAL    SEGMENT SPACE MANAGEMENT auto;

I took a clue from JonathanLewis(each row will require at least 11 bytes (2 bytes row directory entry, 3 bytes row overhead, extra 6 bytes being the pointer to where the migrated row and no bytes for data/null values)

the 8085 bytes after using 107bytes will be systematically dividing to fill the block and allow up to 105 concurrent updates without row contention/lock.  Column of char 66 bytes will be created. Each will have overhead of about 11 bytes.

107 bytes is the block overhead that stores transaction entry (at least 1 itl entry{23bytes per it}),table directory & row directory.

create table itl_table (fixed_size77 char(66))  --66bytes for column data + row overhead + row directory (11 bytes)segment creation immediatetablespace ITL_8KBpctfree 0 ;

 itl_table table was created using PCTFREE 0. this to allow insert to used up the whole space(i.e 8085bytes) in data block.

insert into itl_table
select  rownum from dual connect by level <= 105
; commit;
spool block_content
column rel_file_no new_value m_file
column block_no    new_value m_block
select
        dbms_rowid.rowid_relative_fno(rowid)    rel_file_no,
        dbms_rowid.rowid_block_number(rowid)    block_no,
        count(*)                                number_of_rows_address_in_block
from
        itl_table
group by
        dbms_rowid.rowid_relative_fno(rowid),
        dbms_rowid.rowid_block_number(rowid)
order by
        dbms_rowid.rowid_relative_fno(rowid),
        dbms_rowid.rowid_block_number(rowid)
;

REL_FILE_NO   BLOCK_NO ROWS_STARTING_IN_BLOCK                                
----------- ---------- ----------------------                                             2         13                    105

--DUMP data block

alter system flush buffer_cache;
alter system dump datafile &m_file block &m_block;
 column tracefile new_value m_tracefile
select  tracefile
from   v$process where addr = (
                select paddr from v$session where sid = (
                        select sid from v$mystat where rownum = 1
                )
        )
;

/u04/app/base/diag/rdbms/wro18c/wro18c/trace/wro18c_ora_4610.trc

--Interpreting Data Block Dump. Note this is reverse engineered! 

open /u04/app/base/diag/rdbms/wro18c/wro18c/trace/wro18c_ora_4610.trc in an file editor.

*** 2018-12-23T10:14:52.169907-05:00
Start dump data blocks tsn: 8 file#:2 minblk 13 maxblk 13
Block dump from cache:
Dump of buffer cache at level 3 for pdb=0 tsn=8 rdba=8388621
Block dump from disk:
buffer tsn: 8 rdba: 0x0080000d (2/13)
scn: 0x3879e9 seq: 0x01 flg: 0x06 tail: 0x79e90601
frmt: 0x02 chkval: 0x8ec1 type: 0x06=trans data

first "scn" represent when block is dump

"frmt": 0x02 represent oracle version 8 and later

"chkval" blcok checksum value

type: 0x06=trans data 

Block header dump section:

Block header dump:  0x0080000d
 Object id on Block? Y
 seg/obj: 0x1254c  csc:  0x00000000003879e6  itc: 2  flg: E  typ: 1 - DATA
     brn: 0  bdba: 0x800008 ver: 0x01 opc: 0
     inc: 0  exflg: 0

"itc" number of ITL entries
seg/obj:" object/segment ID
"typ: 1 - DATA" : table.  for index "typ: 2 - INDEX"

data header section

tsiz: 0x1f98
hsiz: 0xe4
pbl: 0x7fa61a96c064
     76543210
flag=--------
ntab=1
nrow=105
frre=-1
fsbo=0xe4
fseo=0x2e2
avsp=0x1fe
tosp=0x1fe
0xe:pti[0]      nrow=105        offs=0
0x12:pri[0]     offs=0x2e2
0x14:pri[1]     offs=0x328
0x16:pri[2]     offs=0x36e
...
...
...
 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x0003.005.0000062f  0x010003e1.019b.18  --U-  105  fsc 0x0000.003879e90x02   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000bdba: 0x0080000d

  

"tsiz" available space for row data

avsp = available space for data

tosp = total space available

ntab = number of table(always 1 except for cluster table)

nrow = row number

offs=0x2e2  = row directory, for a quick search of row data in block.

Itl = transaction entries

Xid = transaction id

Scn= last scn for Itl entries

block_row_dump section: content actual row database

tab 0, row 2, @0x36e
tl: 70 fb: --H-FL-- lb: 0x1  cc: 1
col  0: [66]
 33 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
tab 0, row 3, @0x3b4

"@0x36e"  directory id for lookup: part of identified row(ROWID)

"tl" total number of bytes for this row + overhead (in our case, char 66 bytes + at least 3bytes overhead according to oracle doc.)

"cc" number of column in column data section of row data/piece.

"fb" flags show: H-row header,F- first piece,L-last piece (this indicator for chained and migrated rows)

col  0: [66] -- shows the bytes size for row data. fixed for char.

33 equals 3 .the actual data are in hexadecimal, can be lookup in ASCII table.

there are trailing 20 (ascii=space), because CHAR(66) have to use all the space up not like varchar2

Simulating ITL contention.

I wrote simple codes to automatic workload generation that will cause ITL contention.

--Sequence to specify row_id number, just for convenient

DROP SEQUENCE row_number;
CREATE SEQUENCE row_number START WITH 0 INCREMENT BY 1 MINVALUE 0 MAXVALUE 105 NOCYCLE;

--SQL Script for updating specify rowid in block without causing row contention. runs for 5 minutes

SET TIMING ON
SET SERVEROUTPUT ON
SET LINE 400 PAGESIZE 400
ALTER SESSION SET nls_date_format = 'yyyy-mm-dd hh24:mi:ss';
SELECT
    SYSDATE   start_time FROM    dual;
DECLARE
    row_number_id   NUMBER;
    counter         DATE;
BEGIN
    SELECT
        row_number.NEXTVAL   INTO row_number_id  FROM dual;
    SELECT        SYSDATE    INTO counter    FROM        dual;
    WHILE counter + 5 / 1440 > SYSDATE LOOP
        UPDATE itl_table
        SET
            fixed_size77 = (
                SELECT  TO_CHAR(dbms_random.value(low => 1,high => 10E15) )  FROM dual
            )
        WHERE
            dbms_rowid.rowid_row_number(ROWID) = row_number_id;
         COMMIT;
    END LOOP;
END;
/
 SELECT
    SYSDATE   end_time,  row_number.CURRVAL FROM    dual;
EXIT

--Bash to automatic sqlscrpt load and awr_snapshot.

SQL_SCRIPT=$1
sqlplus -s tobi/tobi @start_awr_snapshot.sql
for i in `seq 1 32` # Repeat runs to observe variation.
dosqlplus -s tobi/tobi @$SQL_SCRIPT &done
sleep 30
while true; do if [ `ps -ef|grep itl_run.sql|wc -l`  -eq 1 ] ; then
sqlplus -s tobi/tobi @end_awr_snapshot.sql;
break; fi; done

--start_awr_snapshot.sql

ALTER SYSTEM SET STATISTICS_LEVEL=ALL;
EXEC DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT;

--end_awr_snapshot.sql

EXEC DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT;
ALTER SYSTEM SET STATISTICS_LEVEL=TYPICAL;

--DUMP BLOCK AFTER the first load run with 105 concurrent session.

Oracle create 21(total now is 23) additional transaction entries based on the available space, and even shrink row data/tsiz to 7584 from 8088 bytes.

as oracle docs says "In data blocks allocated to segments that support transactional changes, free space can also hold transaction entries when the header space is depleted"

you can check ITL count by dumping the block and search for "itc".

Now, going to test implication of ITL contention on a block (and generally speaking table segment) using AWR to measure.

case 1: will use 16 concurrent session. less than ITL available (current available is 0x17 which is 23 in decimal)

case 2: will use 32 concurrent session. greater than ITL available

--analysis of AWR

for 16 concurrent session AWR reported no ITL waits event! e.g. allocate ITL entry.

also segment by waits report no segment under these sessions

Segments by ITL Waits                   DB/Inst: WRO18C/wro18c  Snaps: 138-139
                   No data exists for this section of the report.
                          ------------------------------------------------------

For 32 concurrent session AWR reported "TX-Transaction (allocate ITL entry)"= scarcity of ITL resources in block.

enq: TX - allocate ITL entry           499         30   60.11ms     .3 Configur
..
Segments by ITL Waits                   DB/Inst: WRO18C/wro18c  Snaps: 140-141
-> % of Capture shows % of ITL waits for each top segment compared
-> with total ITL waits for all segments captured by the Snapshot
-> When ** MISSING ** occurs, some of the object attributes may not be available
                      Tablespace
Owner                   Name
-------------------- ----------
                     Subobject  Obj.                                 ITL    % of
Object Name            Name     Type        Obj#   Dataobj#        Waits Capture
-------------------- ---------- ----- ---------- ---------- ------------ -------
TOBI                 ITL_8KB
ITL_TABLE                       TABLE      75084      75084          499  100.00
                          ------------------------------------------------------

--Case 3: will delete couple of entries in the table(4 row). this is to prove that even if you have a higher number of concurrent session, if you have sufficient ITL entries available, ITL allocation will not be a bottleneck for your concurrent transactions.

each transaction entries occupies 23bytes. we can make room for 9 more entries by delete 4 rows(4*70 > 23*9).

DELETE itl_table
WHERE
    dbms_rowid.rowid_row_number(ROWID) IN (
        104,
        103,
        102,
        101
    );  /** remember rowid start from 0. we have 105 rows in the itl_table. this should lead to free atleast 4* 70(66 column size + overhead) bytes **/
 COMMIT;

after block dump shows:

avsp=0x6  (only 6 bytes available for data, oracle algorithm is intelligent enough to see this is hot block, definitely a flag is set to ON in the block meta data that signify space pressure for ITL entries allocation; avsp is 0x6bytes instead of 0x11e total space available) 

tosp=0x11e  (total available space available)

After re-executing the 32-concurrent session workload again, transaction entries increased by 10 making the current transaction entries count 33.

..
itc: 33
..
avsp=0x36
tosp=0x36

mathematically, 286(tosp=0x11e total space available before running 32 concurrent session) - 23(bytes for each itl)*10(number of newly itl created)

The AWR report shows no "enq: TX - allocate ITL entry" events...even though 32 concurrent sessions! ITL have been gracefully increased to 32.

Segments by ITL Waits                   DB/Inst: WRO18C/wro18c  Snaps: 147-148

                   No data exists for this section of the report.
                          ------------------------------------------------------

Summary:

when creating table with high update (and insert) consider INITRANS value in your create table statements.

Also PCTFREE is another solution to ITL contention, because oracle can decide to use "free space" in data block for ITL entries.

you can also see importance of ITL for migrated rows and compression at JL