Blocking Locks and FK Indexing
We recently upgraded a database to 10.2.0.4, and delivered a new vendor supplied schema. We seem to be experiencing more blocking lock situations since the upgrade.
We are looking at traces and investigating serveral solutions and would like an opinion on one general possibility.
There are several hundred un-indexed foriegn key columns in the new database. Oracle docs seem to conflict on whether this could be a contributing factor. The Developers Guide says to "Index foreign keys unless the matching unique or primary key is never updated or deleted". In our case, PK columns are defintely not updated, and there is very little deleting overall.