Oracle and Foreign Keys without Index

Unless there is an index on the foreign key constraint columns Oracle will use TM enqueues to establish a lock in the session that performs the change. These locks reduce the scalability as they lock the whole table and therefore prevent all other sessions from changing unrelated records. Oracle held the lock for the duration of the whole transaction in older releases. But this has improved greatly with Oracle 10g and the lock is enforced for a much smaller time now.

Oracle 11g (tested on 11.2) introduced another change to the locking code. In our test for the migration to 11g we had frequent application freezes. Analysis revealed that there seems to be a change in the lock modes. This leads to heavier TM enqueue contention and caused a freeze when sessions did not commit for a longer period of time. A test case showed that a missing foreign key index on the child table caused the behaviour and creating the index solved the issue instantly. Luckily we found the cause before the application went live on 11g.

Oracle index guru Richard Foote has a note on the locking modes when policing FK constraints where all the internals are explained.

The issue made me rework my script to locate missing foreign key indexes. There are a couple of scripts already available on other sites. But most of the time these scripts either check only the current schema or they expect the index and the constraint to have the exact same columns in the exact same order. But a test easily shows that the index may in fact have trailing columns and the constraint columns may appear in any order as long as they are the leading columns in the index.

So here is my script fkeyidx.sql to check for missing foreign key indexes in Oracle. It uses listagg() and therefore needs Oracle 11g.