Tuesday, September 16, 2014

Enq: TM - contention waits in oracle database and solution !

Today I was noticed that, one delete (5 rows ) statement taking so much time in a particular table only. then I looked into it, found  "Enq: TM - contention" waits taking times .....
In this table there is a foreign key relationship with other table.
and  "Enq: TM - contention" indicate there are unindexed foreign key constraints. so I just created an index on that foreign key column or columns (in foreign table) it solved the problem.

Here is a script that can find  unindexed foreign key constraints for a specific user .....

SELECT * FROM (
SELECT c.table_name, cc.column_name, cc.position column_position
FROM   user_constraints c, user_cons_columns cc
WHERE  c.constraint_name = cc.constraint_name
AND    c.constraint_type = 'R'
MINUS
SELECT i.table_name, ic.column_name, ic.column_position
FROM   user_indexes i, user_ind_columns ic
WHERE  i.index_name = ic.index_name
)
ORDER BY table_name, column_position;