Oracle Locking Mechanism – Latches & Enqueues

This article is part of Oracle Performance Tuning Series and is created to describe Oracle’s locking mechanism. A lock is a resource that you need to hold to get access to the resource. Oracle has two kind of locks: enqueues and latches, we would now focus on these locking mechanism individually. Enqueues Enqueues are sophisticated…

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…

row cache lock – Oracle Wait Event

The Row Cache or Data Dictionary Cache is a memory area in the shared pool that holds data dictionary information to reduce physical I/O on the data dictionary tables. The row cache lock is used primarily to serialize changes to the data dictionary and is waited on when a lock on a data dictionary cache…

log file sync – Oracle Wait Event

When a user session commits, the session’s redo information needs to be flushed from memory to the redo logfile to make it permanent. At the time of commit, the user session will post the LGWR to write the log buffer (containing the current unwritten redo, including this session’s redo information) to the redo log file.…

log file switch – Oracle Wait Event

log file switch (archiving needed) Waiting for a log switch because the log that the LGWR will be switching into has not been archived yet. Check the alert file to make sure that archiving has not stopped due to a failed archive write. To speed archiving, consider adding more archive processes or putting the archive…

Library Cache Pin – Oracle Wait Event

Library cache pins are used to manage library cache concurrency. Pinning an object causes the heaps to be loaded into memory (if not already loaded). PINS can be acquired in NULL, SHARE or EXCLUSIVE modes and can be considered like a special form of lock. A wait for a “library cache pin” implies some other…