Contents Lab128 - Tools for Advanced Oracler Tuning and Monitoring. Reference Guide.

Lock and Locked Object Details

The Locks and Locked Objects window provides details about locking activity. This data originates from the v$lock view. When an object ID is supplied, Lab128 internally fetches the name and type of locked objects. It also highlights the blocking and waiting sessions, helping you to make a decision on how to resolve the locking conflict.

Below is an example of this window showing Blocker and Waiter. The picture also shows a hot spot on the Lock list - SID column. Clicking the hot spot will call the Session details window and will select the corresponding session.

In this picture, session 156 is holding two locks: TM and TX type. Another session, SID=139, is holding two TM locks and is trying to get an exclusive TX lock held by session 156. Therefore it is blocked by session 156 and this is indicated in the column "Blocked by". Yet another session, SID=152, is trying to get an exclusive TX lock held by session 139, therefore it is blocked by 139. Decoding highlight color, session 156 is a Blocker, while session 139 is a Blocker and Waiter at the same time. Session 152 is a Waiter. TM locks can be linked to the database object; in this case there are two tables involved ADM_A and ADM_B. From this point, check the intended activity of contended sessions by drilling-down to the Session Details window using links in the SID column.

Viewing options.

Oracle has two types of locks (enqueues): "System enqueues" and "User enqueues". The type is derived from the v$lock.TYPE column as following: The user type locks are: TM - DML enqueue; TX - Transaction enqueue; UL - User supplied. All others are System locks. Since System locks are less interesting from a DB user's point of view, by default the System enqueue check box is unchecked.

Descriptions of the columns in the Locks tabular view.

Note: All joins between data from different sources are performed internally in Lab128 to offload the monitored instance from unnecessary work.