Solve "Enq: TM Contention" when unindexed foreign keys is the cause

If you notice a very slow behaviour of your database and, from the multiple ways you can go further,  you issue the following command

SELECT l.sid, s.blocking_session blocker, s.event, l.type, l.lmode, l.request, o.object_name, o.object_type
FROM v$lock l, dba_objects o, v$session s
WHERE UPPER(s.username) = UPPER('&User')
AND l.id1 = o.object_id (+)
AND l.sid = s.sid
ORDER BY sid, type;

and you receive a line like this:

SID   BLOCK   EVENT                  TYPE  MODE  REQ  OBJECT     OBJECT_TYPE
83    54      enq: TM - contention   TM    0     2    STUDENTS   TABLE


you should take care of this problem, because will not disappear by itself.

First, locate the unindexed foreign keys:


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

and take the appropriate action, either drop some foreign key constraints if you don't need them  or create the proper indexes. 
Good luck!