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