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.

Purpose

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:

You need to decode if an instance has had locking problems with enqueues. See section "Has locking been a problem".
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.