0

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?

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.