[Solved] How can I find missing Foreign Key indexes?

This query will give you a list of all tables and cols which have FK references to table 'FOO' and are not indexed. Adding indexes on these should solve your TM contention.

with fks as (
  SELECT UC.TABLE_NAME referer_tbl,
  FK_CONS_COL.COLUMN_NAME referer_col,
  UC.CONSTRAINT_NAME referer_constr,
  PK_CONS_COL.TABLE_NAME referred_tbl,
  PK_CONS_COL.COLUMN_NAME referred_col,
  PK_CONS_COL.CONSTRAINT_NAME referred_constr
  FROM USER_CONSTRAINTS  UC
  join USER_CONS_COLUMNS PK_CONS_COL on PK_CONS_COL.CONSTRAINT_NAME = UC.R_CONSTRAINT_NAME
  join USER_CONS_COLUMNS FK_CONS_COL on FK_CONS_COL.CONSTRAINT_NAME = UC.CONSTRAINT_NAME
  WHERE uc.constraint_type = 'R'
)
select *
from fks
where referred_tbl in UPPER(('FOO'))
and not exists (
  select 1 from user_ind_columns
  where table_name = referer_tbl and column_name = referer_col and column_position = 1
)
order by 1, 2;

One point which the previous answers miss is the recursive nature of the FKs.  Imagine a relationship like this:

DEPARTMENT

  ID

EMPLOYEE

  ID

  DEPT_ID (FK to DEPARTMENT.ID with DELETE CASCADE)

COMPUTER

  ID

  ISSUED_TO_EMPLOYEE_ID (FK to EMPLOYEE.ID with DELETE CASCADE)

In this situation, if you delete from DEPARTMENT, you need an index on EMPLOYEE.DEPT_ID and an index of COMPUTER.ISSUED_TO_EMPLOYEE_ID.  The previously mentioned queries don't cover that scenario, but the following should.

with fks as (
  SELECT 
  PK_CONS_COL.TABLE_NAME referred_tbl,
  PK_CONS_COL.COLUMN_NAME referred_col,
  PK_CONS_COL.CONSTRAINT_NAME referred_constr,
  UC.TABLE_NAME referer_tbl,
  FK_CONS_COL.COLUMN_NAME referer_col,
  UC.CONSTRAINT_NAME referer_constr,
  UC.DELETE_RULE delete_rule
  FROM USER_CONSTRAINTS UC
  join USER_CONS_COLUMNS PK_CONS_COL on PK_CONS_COL.CONSTRAINT_NAME = UC.R_CONSTRAINT_NAME
  join USER_CONS_COLUMNS FK_CONS_COL on FK_CONS_COL.CONSTRAINT_NAME = UC.CONSTRAINT_NAME
  WHERE uc.constraint_type = 'R'
),
hier_fks as (
  select 
    level lvl,
    referred_tbl,
    case
      when not exists (
        select 1 from user_ind_columns
        where table_name = referer_tbl and column_name = referer_col and column_position = 1
      ) then 0
      else 1
    end has_fk,
    referer_tbl,
    referer_col
  from fks
  start with referred_tbl in UPPER('SINGLETON')
  connect by 
    nocycle prior referer_tbl = referred_tbl 
    and prior delete_rule in ('CASCADE')
  order by level, referred_tbl, referer_tbl, referer_col
)
select distinct * from hier_fks
where has_fk = 0
order by lvl, referred_tbl, referer_tbl, referer_col

Same as above but generates creation SQL:

with fks as (
  SELECT UC.TABLE_NAME referer_tbl,
  FK_CONS_COL.COLUMN_NAME referer_col,
  UC.CONSTRAINT_NAME referer_constr,
  PK_CONS_COL.TABLE_NAME referred_tbl,
  PK_CONS_COL.COLUMN_NAME referred_col,
  PK_CONS_COL.CONSTRAINT_NAME referred_constr,
  'create index ' || SUBSTR(UC.TABLE_NAME, 1, 20) || '_SING_IDX' || ' on ' || UC.TABLE_NAME || ' (' ||FK_CONS_COL.COLUMN_NAME || ');' SQL_TO_CREATE
  FROM USER_CONSTRAINTS  UC
  join USER_CONS_COLUMNS PK_CONS_COL on PK_CONS_COL.CONSTRAINT_NAME = UC.R_CONSTRAINT_NAME
  join USER_CONS_COLUMNS FK_CONS_COL on FK_CONS_COL.CONSTRAINT_NAME = UC.CONSTRAINT_NAME
  WHERE uc.constraint_type = 'R'
)
select *
from fks
where referred_tbl in UPPER(('SINGLETON'))
and not exists (
  select 1 from user_ind_columns
  where table_name = referer_tbl and column_name = referer_col and column_position = 1
)
order by 1, 2;