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:
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]
