Performance Monitoring Data Dictionary Views

This section lists some of the data dictionary views that you can use to monitor an Oracle Database instance. These views are general in their scope. Other views, more specific to a process, are discussed in the section of this book where the process is described.

View	Description
`V$LOCK`	Lists the locks currently held by Oracle Database and outstanding requests for a lock or latch
`DBA_BLOCKERS`	Displays a session if it is holding a lock on an object for which another session is waiting
`DBA_WAITERS`	Displays a session if it is waiting for a locked object
`DBA_DDL_LOCKS`	Lists all DDL locks held in the database and all outstanding requests for a DDL lock
`DBA_DML_LOCKS`	Lists all DML locks held in the database and all outstanding requests for a DML lock
`DBA_LOCK`	Lists all locks or latches held in the database and all outstanding requests for a lock or latch
`DBA_LOCK_INTERNAL`	Displays a row for each lock or latch that is being held, and one row for each outstanding request for a lock or latch
`V$LOCKED_OBJECT`	Lists all locks acquired by every transaction on the system
`V$SESSION_WAIT`	Lists the resources or events for which active sessions are waiting
`V$SYSSTAT`	Contains session statistics
`V$RESOURCE_LIMIT`	Provides information about current and maximum global resource utilization for some system resources
`V$SQLAREA`	Contains statistics about shared SQL area and contains one row for each SQL string. Also provides statistics about SQL statements that are in memory, parsed, and ready for execution
`V$LATCH`	Contains statistics for nonparent latches and summary statistics for parent latches

Identifying Locking Issues
Note: text in this session was copied from toadworld.com

1 Query to show information about locked objects:
SELECT O.OWNER, O.OBJECT_ID, O.OBJECT_NAME, O.OBJECT_TYPE, L.TYPE
FROM DBA_OBJECTS O, V$LOCK L
WHERE O.OBJECT_ID = L.ID1
/

OWNER       OBJECT_ID OBJECT_NAME          OBJECT_TYP TY
---------- ---------- -------------------- ---------- --
SYSTEM          32392 FOOBAR               TABLE      TM
SYSTEM          32392 FOOBAR               TABLE      TM
...
SYSTEM          32371 MY_DEPT              TABLE      TM

2 DBA_BLOCKERS

The DBA_BLOCKERS view has one column, HOLDING_SESSION, which indicates each session that is:

Not currently waiting on a locked object, and
Currently holding a lock on an object for which another session is waiting.

3 DBA_WAITERS

The DBA_WAITERS view is the counterpart to DBA_BLOCKERS. It indicates which sessions are:

Currently waiting on a locked object, and
Not currently holding a lock on an object for which another session is waiting.

The WAITING_SESSION column indicates the SID of the waiting session, and the HOLDING_SESSION column indicates the SID of the holding session. The view shows the lock type, and the modes in which the lock is being held and requested.

Oracle Locking Survival Guide

Note: text below was copied from this website, which is likely copied from akadia.com

This is the original Oracle script to print out the lock wait-for graph in a tree structured fashion. This script prints the sessions in the system that are waiting for locks, and the locks that they are waiting for. The printout is tree structured. If a sessionid is printed immediately below and to the right of another session, then it is waiting for that session. The session ids printed at the left hand side of the page are the ones that everyone is waiting for (Session 96 is waiting for session 88 to complete):
WAITING_SESSION   LOCK_TYPE    MODE_REQUESTED MODE_HELD  LOCK_ID1  LOCK_ID2
----------------- ------------ -------------- ---------- --------- --------
88                None
   96             Transaction  Exclusive      Exclusive  262144    3206

The lock information to the right of the session id describes the lock that the session is waiting for (not the lock it is holding). Note that this is a script and not a set of view definitions because connect-by is used in the implementation and therefore a temporary table is created and dropped since you cannot do a join in a connect-by.

This script has two small disadvantages. One, a table is created when this script is run. To create a table a number of locks must be acquired. This might cause the session running the script to get caught in the lock problem it is trying to diagnose. Two, if a session waits on a lock held by more than one session (share lock) then the wait-for graph is no longer a tree and the conenct-by will show the session (and any sessions waiting on it) several times.