Hi All,
I'm hoping for some help as I'm investigating an issue currently with RAC and enq: TM Contention. The platform is a highly busy system and is suffering stability issues and when it has issues enq: TM Contention appears to be the culprit.
I've been investigating this wait event and have concluded that the general cause is unindexed Foreign Keys. I have checked the DDL originally used to create the table and the FK constraints and then checked each of the Child Tables to see if the Column used as the FK is indexed. On the Child table it does appear to be Indexed. However the same column within the Parent table does not appear to be Indexed.
So far all the research I've done has focused on the Child tables. I've used a script provided by Tom Kyte to find unindexed FKs and it does find a number of columns which is deems unindexed but as I say Child table is indexed but parent is not. Before I suggest we index the column on the Parent can you confirm whether this is likely to alleviate the TM Contention?
Many Thanks