enq: TX – index contention – Oracle wait event

If you performed lot of DMLs (specially Deletes) on a table, you may have lots of empty leaf blocks in the associated indexes on that table. These empty leaf blocks are left in the b-tree structure AND attached to the freelist. On subsequent inserts, the process might need to split a block so it takes…

enq: TM – contention – Oracle wait event

Oracle acquires a table lock on a child table if it’s performing modifications on the primary key column in the parent table that’s referenced by the foreign key of the child table. And these locks on the child table are full table locks (TM) i.e. these locks aren’t restricted to any row but to the…