My Oracle Support Banner

Tracing Sessions that are Waiting on an Enqueue or a Lock (Doc ID 102925.1)

Last updated on OCTOBER 22, 2022

Applies to:

Gen 1 Exadata Cloud at Customer (Oracle Exadata Database Cloud Machine) - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Oracle Database Backup Service - Version N/A and later
Oracle Database Cloud Exadata Service - Version N/A and later
Oracle Database Cloud Service - Version N/A and later
Information in this document applies to any platform.


NOTE: In the images and/or the document content below, the user information and data used represents fictitious data from the Oracle sample schema(s) or Public Documentation delivered with an Oracle database product.  Any similarity to actual persons, living or dead, is purely coincidental and not intended in any manner.

How to monitor and trace locking problems (enqueues).

NOTE: This document is provided as legacy information from the Oracle 8i-9i Timeframe. Although commands in this document may still function, you are recommended to use articles designed specifically for later versions.

This bulletin will assist you when:

  1. You need to decode if an instance has had locking problems with enqueues. See section "Has locking been a problem".
  2. Are there sessions just now in the instance that are waiting on enqueues and who are the blockers. See "Is locking currently a problem". 

To get better performance, one approach is to eliminate the time a session is waiting to get a resource (for example an enqueue). You don’t want sessions that are waiting on other sessions, because those waiting sessions may also hold enqueues and resources, which will make other sessions wait for them - which can end up with a deadlock.

Troubleshooting Steps

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!

In this Document
Troubleshooting Steps
 What are locks, enqueues and latches
 Common types of enqueues
 Has locking been a problem?
 Is locking a problem currently?
Get some common information about these sessions from V$SESSION
 Get information about the enqueues the sessions are holding and waiting on
 What are the sessions doing?
 Get some information about the Blocker's transaction
 Which SQL statements are the sessions currently executing (or last executed)
 Get all open cursors for the two session
 Get some information about the Blocker’s session so you can call him and tell him to COMMIT/ROLLBACK his transaction.

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.