Oracle Database Discussions

I joined a new project recently. I checked prod and our biggest bottleneck is unindexed foreign keys. It is high enough that I can see that it is causing problems. So I ran a query and got a list of all the unindexed foreign keys. Unfortunately there are about 80 of them. This application was inherited. The last team lost the project and I think one of the issues is with an off the shelf application (which we can't get rid of).

I really don't like the idea of adding 80 indexes in a big rollout. It is too big of a change to do at once. It is also hard to measure whether those indexes may cause other problems. So what I would like to do is take my Enqueue waits for unindexed foreign keys and somehow figure out which unindexed foreign keys are causing us the biggest problem. With this many there is a strong possibility that some of these tables are having their parents hit more than others and some of these tables are blocking other sessions more than others.

any suggestions on how to do this? It doesn't need to be exact. However, Id like to propose adding indexes that will give us the biggest bang for the buck.

I am not sure how to take my system wide enqueue/deque waits down to particular tables being hit with DML that cause locking on child tables that in turn cause other sessions to be blocked.