Oracle wait type enq: TM – contention

oracleoracle-12cwait-types

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?

Best Answer

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');
Related Question