Hello,
I have seen a strange problem and resolution of which I am trying to make sense of, but am at a loss. The fact that I do not have access to application source code is making things even more difficult.
However, let me try to state the symptoms that I saw in chronological order.
1) My environment is oracle database 10.2.0.4 on Solaris 10 (x86-64), 3-node RAC cluster.
2) The application consists of a couple of processes (one batch job and another a small routine that polls frequently) which add/change data in a pair of parent-child tables. The exact statements are UPDATE on parent table (but not to PK), SELECT...FOR UPDATE on child table and UPDATE on child table (but not to FK column pointing to PK column of parent table).
3) Recently, we decided to drop the index on FK constraint column on child table because it was causing one of the queries against child table to use bad plan occassionaly.
4) However, after the FK index was dropped, the above 2 processes started blocking each other heavily with the event "enq: tm contention" mostly on child table (the value of p2).
5) It was suggested that we put the index back on the FK column on child table and doing that has actually resulted in all "enq: tm contention" disappearing.
Now, my question is as our processes are neither updating PK column value in parent table nor updating corresponding FK column value in child table, why should I observe lots of locking with "enq: tm contention" ?
I can confirm that none of these processes (or for that matter any other process) are using parallel DML on either parent or child table when the "enq: tm contention" was observed.
Can somebody please help?
Thanks in advance.