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.

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.

You can use the following script to a create index script for the unindexed foreign key . The purpose of creating such indexes is to prevent enq tm – contention wait events.

 

 

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

 

 5,081 views last month,  1 views today

About Mehmet Salih Deveci

I am Founder of SysDBASoft IT and IT Tutorial and Certified Expert about Oracle & SQL Server database, Goldengate, Exadata Machine, Oracle Database Appliance administrator with 10+years experience.I have OCA, OCP, OCE RAC Expert Certificates I have worked 100+ Banking, Insurance, Finance, Telco and etc. clients as a Consultant, Insource or Outsource.I have done 200+ Operations in this clients such as Exadata Installation & PoC & Migration & Upgrade, Oracle & SQL Server Database Upgrade, Oracle RAC Installation, SQL Server AlwaysOn Installation, Database Migration, Disaster Recovery, Backup Restore, Performance Tuning, Periodic Healthchecks.I have done 2000+ Table replication with Goldengate or SQL Server Replication tool for DWH Databases in many clients.If you need Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS Consultancy and Training you can send my email adress [email protected].-                                                                                                                                                                                                                                                 -Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS ve linux Danışmanlık ve Eğitim için  [email protected] a mail atabilirsiniz.

Leave a Reply

Do NOT follow this link or you will be banned from the site!