I wrote about my problems with Informix. Now we are facing similar problem on Oracle. It results in deadlock. The SQLs are more complex, but core problem is still in full table scan on small data locking the whole table because of full table scan.
We have to find config setting to force Oracle to always use indexes. We know only hint, that increases chance of using indexes – ALTER SESSION SET OPTIMIZER_INDEX_COST_ADJ = 1.
Advice1: don’t use micro benchmark.
Advice2: Don’t slouch on deadlock, you should be able to live them. It’s compromise for gained performance.
Solution: If an index exists on the foreign key column of the child table, no DML locks, other than a lock over the rows being modified, are required.
If the index is not created, a share lock is taken out on the child table for the duration of the transaction.
The referential integrity validation could take several minutes or even hours to resolve. The share lock over the child table will allow other users to simultaneously read from the table, while restricting certain types of modification. The share lock over the table can actually block other normal, everyday modification of other rows in that table.