Solving Waits on "enq: TM - contention"

Symptoms
High "enq: TM - contention"  lock waits   on insert  statments

Cause :
Waits on "enq: TM - contention" indicate there are unindexed foreign key constraints

Solution:
Create  index  on Foreignk Key columns .Following script will find  Unindexed Foreign Key Constraints

SELECT * FROM (
SELECT c.owner,c.table_name, cc.column_name, cc.position column_position
FROM DBA_constraints c, DBA_cons_columns cc
WHERE c.constraint_name = cc.constraint_name
AND c.constraint_type = 'R'
and c.owner not in ('SYS','SYSMAN','SYSTEM')
MINUS
SELECT i.owner,i.table_name, ic.column_name, ic.column_position
FROM DBA_indexes i, DBA_ind_columns ic
WHERE i.index_name = ic.index_name
)
ORDER BY table_name, column_position
/


Reproduce :

create table departments
(
dept_no number,
dept_name varchar2(100),
CONSTRAINT dep_no_pk PRIMARY KEY (dept_no)
);

insert into departments values (1,'Department 1');
insert into departments values (2,'Department 2');
insert into departments values (3,'Department 3');
commit;

create table employees (
emp_id number,
emp_name varchar2(100),
emp_dept_no number,
CONSTRAINT fk_departments FOREIGN KEY (emp_dept_no) REFERENCES departments(dept_no) ON DELETE CASCADE
);

insert into employees values (1,'Serdar Turgut',1);
insert into employees values (2,'Erkan saka',2);
insert into employees values (3,'Metin Yavas',2);
insert into employees values (4,'Abdullah Ongul',3);
commit;

SESSION 1:delete departments where dept_no=1 ;
SESSION 2:delete departments where dept_no=2 ;
SESSION 3:insert into departments values (4,'Dept 4 ');

column type format a4
column lmod format a4
column request format 999
SELECT l.sid, s.blocking_session blocker, SUBSTR(s.event,1,20),
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('SERDAR')
AND l.id1 = o.object_id (+)
AND l.sid = s.sid
ORDER BY sid, type;

SID BLOCKER SUBSTR(S.EVENT,1,20) TYPE LMODE REQUEST OBJECT_NAME OBJECT_TYPE
-------- --------- -------------------- ---- --------- ------- ------------------------------ -----
4859 SQL*Net message from TM 3 0 DEPARTMENTS TABLE
4859 SQL*Net message from TM 3 0 EMPLOYEES TABLE
4859 SQL*Net message from TX 6 0
4902 4909 enq: TM - contention TM 0 2 EMPLOYEES TABLE
4902 4909 enq: TM - contention TM 3 0 DEPARTMENTS TABLE
4909 4859 enq: TM - contention TM 0 5 EMPLOYEES TABLE
4909 4859 enq: TM - contention TM 3 0 DEPARTMENTS TABLE


See also:All Oracle enqueue waits