Investigating ITL wait resulted from ORA-00060: Deadlock detected

Hello,
I got the following error:ORA-00060: Deadlock detected
investigating the trace logfile i found :

Deadlock graph:

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

Resource Name          process session holds waits  process session holds waits

TX-0013001c-0002687a       149     369     X            135     517           S

TX-000c0004-000283db       135     517     X             99     531           S

TX-00090003-0005c060        99     531     X            149     369           S

the session Holds wait type S made me check if it resulted from ITL wait and ehre i checked it using the following query:

select * from

(

   select 

      DECODE

      (GROUPING(a.object_name), 1, 'All Objects', a.object_name)

   AS "Object",

sum(case when 

   a.statistic_name = 'ITL waits' 

then 

   a.value else null end) "ITL Waits",

sum(case when 

   a.statistic_name = 'buffer busy waits' 

then 

   a.value else null end) "Buffer Busy Waits",

sum(case when 

   a.statistic_name = 'row lock waits' 

then 

   a.value else null end) "Row Lock Waits",

sum(case when 

   a.statistic_name = 'physical reads' 

then 

   a.value else null end) "Physical Reads",

sum(case when 

   a.statistic_name = 'logical reads' 

then 

   a.value else null end) "Logical Reads"

from 

   v$segment_statistics a

where 

   a.owner like upper('&owner')

group by 

   rollup(a.object_name)) b

where (b."ITL Waits">0 or b."Buffer Busy Waits">0);

The owner in the above query is the schema that the statement in the trace file runs against.

Some other queries that may help:

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;

select SID,TYPE,ID1,ID2,LMODE,REQUEST from v$lock                                                            
where type='TX';                                                                                            

Select
   s.sid SID,
   s.serial# Serial#,
   l.type type,
   ' ' object_name,
   lmode held,
   request request
from
   v$lock l,
   v$session s,
   v$process p
where
   s.sid = l.sid and
   s.username <> ' ' and
   s.paddr = p.addr and
   l.type <> 'TM' and
   (l.type <> 'TX' or l.type = 'TX' and l.lmode <> 6)
union
select
   s.sid SID,
   s.serial# Serial#,
   l.type type,
   object_name object_name,
   lmode held,
   request request
from
   v$lock l,
   v$session s,
   v$process p,
   sys.dba_objects o
where
   s.sid = l.sid and
   o.object_id = l.id1 and
   l.type = 'TM' and
   s.username <> ' ' and
   s.paddr = p.addr
union
select
   s.sid SID,
   s.serial# Serial#,
   l.type type,
   '(Rollback='||rtrim(r.name)||')' object_name,
   lmode held,
   request request
from
   v$lock l,
   v$session s,
   v$process p,
   v$rollname r
where
   s.sid = l.sid and
   l.type = 'TX' and
   l.lmode = 6 and
   trunc(l.id1/65536) = r.usn and
   s.username <> ' ' and
   s.paddr = p.addr
order by 5, 6;

If you got IT 0 waits then it is not ITL wait problem but if you find that it is ITL problem then you have to refer to the following note:

How To Solve High ITL Waits For Given Segments. [ID 464041.1]