enq: TM – contention – Oracle wait event

Oracle acquires a table lock on a child table if it’s performing modifications on the primary key column in the parent table that’s referenced by the foreign key of the child table. And these locks on the child table are full table locks (TM) i.e. these locks aren’t restricted to any row but to the entire table.

Now the problem occurs when other sessions try to modify this child table they see a full table lock on the child table and thus have to wait for the lock to be released. This wait is denoted as enq: TM contention.

Now to resolve these waits we have to ensure that those full table locks are avoided and the easiest way to do it is by indexing the unindexed foreign key constraint

Using below query you can find all unindexed foreign key constraints in a specific schema:-

select * from (
select ct.table_name, co.column_name, co.position column_position
from user_constraints ct, user_cons_columns co
where ct.constraint_name = co.constraint_name
and ct.constraint_type = 'R'
minus
select ui.table_name, uic.column_name, uic.column_position
from user_indexes ui, user_ind_columns uic
where ui.index_name = uic.index_name
)
order by table_name, column_position;

 

 

Advertisements

One thought on “enq: TM – contention – Oracle wait event

  1. Pingback: Oracle Wait Events | Oracle Database Internal Mechanism

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.