Foreign keys produce potentially damaging locking problems if the foreign key columns on the child table are not indexed. Below query lists all of the foreign keys that do not have the appropriate indexes in place on the child table. It shows the foreign key constraints that cause locking problems.
SELECT acc.owner||'-> '||acc.constraint_name||'('||acc.column_name
||'['||acc.position||'])'||' ***** Missing Index'
FROM all_cons_columns acc, all_constraints ac
WHERE ac.constraint_name = acc.constraint_name
AND ac.constraint_type = 'R'
AND (acc.owner, acc.table_name, acc.column_name, acc.position)
IN
(SELECT acc.owner, acc.table_name, acc.column_name, acc.position
FROM all_cons_columns acc, all_constraints ac
WHERE ac.constraint_name = acc.constraint_name
AND ac.constraint_type = 'R'
MINUS
SELECT table_owner, table_name, column_name, column_position
FROM all_ind_columns)
ORDER BY acc.owner, acc.constraint_name,
acc.column_name, acc.position;
By creating an index on the foreign key of the child table, “table-level” locks can be avoided.
Keep in mind that you will often be creating an index on the foreign keys in order to optimize join and queries. However, if you fail to create such a foreign key index and if the parent table is subject to updates, you may see heavy lock contention. If ever in doubt, it’s often safer to create indexes on ALL foreign keys, despite the possible overhead of maintaining unneeded indexes.
Source / Reference links: