You can refer this to create missing foreign key constraint indexes, which help you to get rid of this issue. There is MOS note: Script to Check for Foreign Key Locking Issues for a Specific User (Doc ID 1019527.6) How to identify & fix foreign key constraint indexes: It is mainly due to poor application design. The Enqueue Waits Per Txn Oracle metric represents the total number of waits per transaction that occurred during an enqueue convert or get because the enqueue get was deferred. One more thing we need to keep in mind, these waits does not indicate a problem with the DB itself. Also Oracle waits for the TM lock until it is either granted or the request times out or deadlock detection occurs for TM locks. enq: TO - contention is seen whilst Oracle synchronises DDL and DML operations on temporary objects. First of all, you must find out which type of enqueue is involved: - Capture VSESSIONWAIT for event 'enqueue' - Find out enqueue type select chr(bitand(P1,-16777216)/16777215) chr(bitand(P1,16711680)/65535) 'Name', bitand(P1, 65535) 'Mode' from dual Where P1 VSESSIONWAIT.P1 - Find out which SQL statement the sessions are. ![]() ![]() The most common reason for waits on TM locks tend to involve Foreign Key constraints where the constrained columns are not indexed. An enqueue is a wait for a resource held by another. It is therefore recommended to add indexes for all foreign key defined in the database. When your application has referential integrity and attempts to modify the child/parent table, Oracle will get additional locking on the parent/child table when there is no index on the foreign key. For us a database restart fixed this issue. The Oracle AQ monitoring process that is responsable for changing the state from 'WAITING' to 'READY' (after the delay is expired) wasn't working properly. DML operations require table locks to prevent simultaneous DDL operations that would conflict with the transaction. The messages in the queue stayed the state 'WAITING'. The TM Enqueue (DML enqueue) is acquired by a transaction when a table is modified by any DML operation. Also I observed DB alert log was showing muliple deadlocks for the same queries since the issue was started. He raised a incident with us to check everything is fine from DB end.Īfter checking active database sessions, I found some of update queries were having wait event “enq: TM – contention”. ![]() Recently one of my clients facing weird issue, wherein some of application jobs were timing out.
0 Comments
Leave a Reply. |