Oracle ITL Deadlock Identification & Resolution

Question

I have an Oracle DB package which usually causes what I consider to be an ITL deadlock (Transactional Interested List). The corresponding part of the trace file is below.

Deadlock graph: ---------Blocker(s)-------- ---------Waiter(s)--------- Resource Name process session holds waits process session holds waits TM-0000cb52-00000000 22 131 S 23 143 SS TM-0000ceec-00000000 23 143 SX 32 138 SX SSX TM-0000cb52-00000000 30 138 SX 22 131 S session 131: DID 0001-0016-00000D1C session 143: DID 0001-0017-000055D5 session 143: DID 0001-0017-000055D5 session 138: DID 0001-001E-000067A0 session 138: DID 0001-001E-000067A0 session 131: DID 0001-0016-00000D1C Rows waited on: Session 143: no row Session 138: no row Session 131: no row 

There are no bitmap indexes in this table, so this is not the reason. As far as I can tell, the absence of “rows waiting” plus “S” in the “Waiting for Waiting” column probably indicates that this is an ITL lock. In addition, the table is written quite often (about 8 inserts or updates at the same time, as often as 240 times per minute), so the ITL lock seems to be strong.

I increased the INITRANS parameter of the table and it is indexed to 100 and increased PCT_FREE on the table from 10 to 20 (then rebuilt the indexes), but locks still occur. The deadlock seems to occur most often during the update, but it may just be a coincidence, as I only traced it several times.

My questions are doubled:
1) Is this actually an ITL dead end?
2) If this is an ITL impasse, what else can be done to avoid this?

---

It turns out that this is not an ITL blocking problem at all, but a problem with unindexed foreign keys. I found this thanks to dpbradley's answer, in which I was convinced that this was not an ITL problem, and prompted me to figure out what could be causing other “no-string” deadlocks.

Answer 1

The best indicator of ITL pressure is performance presentation:

 select event, total_waits, time_waited, average_wait from v$system_event where event like 'enq: TX%' order by 2 desc; 

indicates that TX is conflicting, and

 select OBJECT_NAME, SUBOBJECT_NAME, TABLESPACE_NAME, OBJECT_TYPE, STATISTIC_NAME, VALUE from v$segment_statistics where statistic_name = 'ITL waits' and value > 0 order by value desc; 

tables and indexes are shown.

(Like all v$ views, the results begin from the moment the instance was started.)

If this shows that you really have ITL, then the INITRANS and PCTFREE parameters are the main knobs for turning (but INITRANS = 100 sounds pretty high for me and they take up limited space).

If ITL is waiting, this is not a problem, then you need to check the application code.

Answer 2

The best option is to increase it as needed (start from 10 by default and increase by 10). If you see a reduction in ITL expectations, you are set up. Typically, these related parameters are corrected by trial and error in both Oracle and SQL Server. Setting these parameters in real time will not be such a big problem if the resource is not extremely busy. You can use the following query to view after each increment, if the ITL is waiting or leaving or is greatly reduced:

 SELECT t.OWNER, t.OBJECT_NAME, t.OBJECT_TYPE, t.STATISTIC_NAME, t.VALUE FROM v$segment_statistics t WHERE t.STATISTIC_NAME = 'ITL waits' AND t.VALUE > 0 ORDER BY t.value desc; 

We made a few adjustments for the Oracle deadlock scenarios due to the ITL waiting for this method. NOTE. Be sure to rebuild the index if initrans is modified for indexes. Also make sure the statistics are not outdated.

For a quick check, SQL Tuning Advisor can be used to view the full status of the query / index and statistics.