enq: TM - contention but FK index is exist
oradba11Oct 11 2012 — edited Oct 12 2012Hello ,
I am working on 11.1.0.7.0 on AIX box.
some time before database got hang and after killing one session (holding lock on one table) every thing went well.
When i took awr and ash report i was not able to find that sql what holding that table.But
I found few 'enq: TM - contention' in ash report this was due to one delete statement.
I found that this issue can come when foreign key is not indexed..
DELETE FROM PWD_HDR WHERE ( PWD_HDR.CLT_NBR = :1 )
CLT NBR is primary key of this table and this column is reference in nother table (CTL_HDR)
I check that in child table the forengn key is indexed ...
wht can be the issue then ...i need to provide root casue analysis..
wht other things i can check..?