tidbit:Oracle:ITL Deadlock troubleshooting and analysis

How to identify ITL deadlock:

1. Pick slot number (0x40021) at the top

2. Search for it in a deadlock trace file

3. See if wait event is ITL related.

Resolution: Increase INI_TRANS on table or on index.

                       ---------Blocker(s)--------  ---------Waiter(s)---------

Resource Name          process session holds waits  process session holds waits

TX-00040021-00001409        66    2515     X             65    1253           S

TX-0005001b-000014d8        65    1253     X             66    2515           S

    Dumping one waiter:

      inst: 1, sid: 2513, ser: 41

      wait event: 'enq: TX - allocate ITL entry'

        p1: 'name|mode'=0x54580004

        p2: 'usn<<16 | slot'=0x40021

        p3: 'sequence'=0x1409

      row_wait_obj#: 4294967295, block#: 0, row#: 0, file# 0

      min_blocked_time: 0 secs, waiter_cache_ver: 31817