unindexed foreign keys – difference in behaviour between 10g and 11g

As a new Oracle DBA you have to troubleshoot performance issues.Once in a while you might come across tables where the foreign keys are not indexed and this causes performance problems. Below article describes the behaviour between 10g and 11g when you dont have indexes on the foreign key.
As you will see in 11g you will have more performance problems as compared to 10g if your foreign key columns do not have indexes on them and if you are trying to modify the primary key in the parent table.

10g behaviour

No Index on the Foreign Key

Figure 21-8 illustrates the locking mechanisms used by Oracle when no index is defined on the foreign key and when rows are being updated or deleted in the parent table. Inserts into the parent table do not require any locks on the child table.

Unindexed foreign keys cause DML on the primary key to get a share row exclusive table lock (also sometimes called a share-subexclusive table lock, SSX) on the foreign key table. This prevents DML on the table by other transactions. The SSX lock is released immediately after it is obtained. If multiple primary keys are updated or deleted, the lock is obtained and released once for each row.

Note:

Indexed foreign keys only cause a row share table lock (also sometimes called a subshare table lock, SS). This prevents other transactions from exclusive locking the whole table, but it does not block DML on the parent or the child table.

Figure 21-8 Locking Mechanisms When No Index Is Defined on the Foreign Key

11g Behaviour

No Index on the Foreign Key

In the following circumstances, the database acquires a table lock on the child table:

No index exists on the foreign key column of the child table.

For example, assume that hr.departments table is a parent of hr.employees, which contains the unindexed foreign key department_id.

A session modifies a primary key in the parent table (for example, deletes a row or modifies primary key attributes) or merges data into the parent table. Inserts into the parent table do not acquire table locks on the child table.

For example, a database session deletes row 3 from the departments table, as shown in Figure 21-3.