Recently, I was working on a packaged application purchased from a third-party vendor. It is one of those platform agnostic systems that started life on Microsoft SQL Server, and has been ported to Oracle. I spend a lot of my time working with PeopleSoft, so I had a certain sense of déjà vu. However, this application uses referential integrity.
The application was upgraded, and simultaneously Oracle was upgraded to 10g and then exhibited TM contention. It had probably been suffering from TM contention while running on Oracle 9i, but we hadn't realised because Oracle9i only reports 'enqueue'.
From 10g, there are no less that 208 different enqueue wait events, that show the type of lock that the process is waiting for, and sometimes additional information. In my case it was event 175. Events can be listed from v$event_name.
With a little help from my friends I came to realise that the cause of this contention was that the system had foreign key constraints on columns that were not indexed. Having found one example of this, I realised that I needed a way to check the entire data model. The result was the following SQL and PL/SQL script.
Uncommenting the following section will produce a test case that should build two indexes.
GFC_FK_INDEX_CHECK is a working storage script that is to hold results of the tests on each foreign key.
First the script populates the working storage table with all the referential integrity constraints that reference a primary key constraint.
This PL/SQL routine checks each foreign key constraint in the table for each constraint it looks up the referring columns in all_cons_columns and builds a dynamic query that SELECTs the owner and name of an index with the same columns in the same position. The name of that index and the column list is stored on the working storage table. Depending upon how many rows that query returns, a status string is written to the table: No Index/Index Found/Multiple Indexes
This query produces a simple report on each foreign key constraint.
This query is similar to the last, but it produces a report of just largest tables that lack indexes on FK constraints. It show tables more than 10000 rows (according to the CBO statistics), or at least the top 20. These are likely to be most severe offenders.
This query generates a script constraint create index DDL statements that will build the missing indexes. The index will have the same name as the foreign key constraint to which it relates.
The test script correctly reports (in fk_index_check.LST) that there are two foreign keys that require supporting indexes
It produces another script fk_index_build.sql that will build the missing indexes.
When I ran this test script on my problem application, it identified over 200 missing indexes on 900 foreign key constraints, and since building the indexes on tables where I have seen TM locking, I haven't seen any TM locking contention.
The script can be downloaded from the Go-Faster website at http://www.go-faster.co.uk/scripts.htm#fk_index_check.sql
Caveat: Just because you can index a foreign key, doesn't mean that you should. See
http://www.jlcomp.demon.co.uk/faq/fk_ind.htmlThis query produces a simple report on each foreign key constraint.