Oracle wait type enq: TM - contention

Question

I’m administrating an Oracle 12c database which shows the wait type: enq: TM – contention in certain time periods (up to 30% of total waits). During this periods the applications does not allow new logins and users are complaining about timeouts and long response time. enq: TM – contention is related to missing indexes on foreign key constrains. How do I find these missing indexes? Should a DBA solve this or should the software company provide the indexes?

Answer 1

Could be many reasons for TM waits but you can try running this script to check for un-indexed foreign keys first.

select cc.owner, cc.table_name, cc.column_name, cc.position   
from dba_cons_columns cc
where 
 cc.owner not in ('SYS','SYSTEM')
 and position is not null
minus
select i.index_owner, i.table_name, i.column_name, i.column_position
from dba_ind_columns i
where 
 i.index_owner not in ('SYS','SYSTEM');