enq: TM – contention | TM Contention Wait Events in Oracle

Hi,

I will explain enq: TM – contention | TM Contention Wait Events in Oracle in this post.

Read the following post before this, if you want to learn more details about Wait events in Oracle.

Oracle Wait Events and Their Solutions in Oracle Database

enq: TM – contention wait event indicates that there are unindexed foreign key constraints and this wait event is seen during DML on tables with unindexed foreign key.

Oracle locks the table if a DML is made to the main table with the primary key.

To solve this problem, you need to create the indexes for the non-indexed foreign key as follows.

select
case
when b.table_name is null then
'unindexed'
else
'indexed'
end as status,
a.table_name as table_name,
a.constraint_name as fk_name,
a.fk_columns as fk_columns,
b.index_name as index_name,
b.index_columns as index_columns
from
(
select 
a.table_name,
a.constraint_name,
listagg(a.column_name, ',') within
group (order by a.position) fk_columns
from
dba_cons_columns a,
dba_constraints b
where
a.constraint_name = b.constraint_name
and 
b.constraint_type = 'R'
and 
a.owner = '&&schema_owner'
and 
a.owner = b.owner
group by 
a.table_name, 
a.constraint_name
) a
,(
select 
table_name,
index_name,
listagg(c.column_name, ',') within
group (order by c.column_position) index_columns
from
dba_ind_columns c
where 
c.index_owner = '&&schema_owner'
group by
table_name, 
index_name
) b
where
a.table_name = b.table_name(+)
and 
b.index_columns(+) like a.fk_columns || '%'
order by 
1 desc, 2;

If there is foreign key, you need to create the index for it using the above script, but sometimes there is no foreign key, you should kill the related session in this case.

You can use the following script to kill locked session.

select 'alter system kill session ''' || a.SID || ',' || a.serial# || ',@'||a.inst_id||''';'
from gv$session a, gv$locked_object b, dba_objects c 
where b.object_id = c.object_id 
and a.sid = b.session_id
and OBJECT_NAME='TABLE_NAME';

Script output is as follows.

 alter system kill session '7211,17842,@5';

Run it as follows, the session will complete after you kill the related session.

SQL> alter system kill session '7211,17842,@5';

System altered.

SQL>

Do you want to learn Oracle Database Performance Tuning detailed, then read the following articles.

Performance Tuning and SQL Tuning Tutorial in the Oracle Database