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.
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.
5,354 views last month, 2 views today