Monitoring Locks and Latches

Monitoring latches and locks can be a challenge in Oracle. Just for V$LOCK DPT alone, multiple joins are usually required to get to the information you desire. I suggest running the CATBLOCK.SQL script, as it creates several useful views for locks. The CATBLOCK.SQL script is located in the /oracle/rdbms/admin directory on UNIX, and in the c:\orant\rdbmsxx\admin directory on Windows.

Also see my notes on finding Oracle blocking sesssions

The catblock.sql script vreates DBA_KGLLOCK, DBA_LOCK, DBA_LOCK_INTERNAL, DBA_DML_LOCKS, DBA_DDL_LOCKS, DBA_WAITERS, and DBA_BLOCKERS. I suggest executing this script with echo set to ON, since in many releases it contains errors that you must correct before it will run properly.

OEM contains a detailed lock screen in the GUI, as well as an HTML-based report for locking. The OEM Lock Manager GUI is shown in Figure 11.8.

Figure 11.8 OEM Lock Manager screen.

Monitoring Sessions Waiting for Locks

If you run the catblock.sql script, which is located in the $ORACLE_HOME/rdbms.admin directory on UNIX or Linux, you will have access to the dba_waiters view. The dba_waiters view gives information on sessions waiting for locks held by other sessions. By joining v$session with dba_waiters, you can obtain detailed information about the locks and sessions that are waiting. A report on this information is shown in Source 11.35.

SOURCE 11.35 Script to report sessions waiting for locks.

rem NAME: waiters.sql
rem FUNCTION: Report on sessions waiting for locks
rem HISTORY: MRA 1/12/96 Creation
rem          MRA 10/14/01 Updated for Oracle9i
rem
COLUMN busername        FORMAT a10      HEADING 'Holding|User'
COLUMN wusername        FORMAT a10      HEADING 'Waiting|User'
COLUMN bsession_id                      HEADING 'Holding|SID'
COLUMN wsession_id                      HEADING 'Waiting|SID'
COLUMN mode_held        FORMAT a10      HEADING 'Mode|Held'
COLUMN mode_requested   FORMAT 999999   HEADING 'Mode|Requested'
COLUMN lock_id1         FORMAT 999999   HEADING 'Lock|ID1'
COLUMN lock_id2         FORMAT a15      HEADING 'Lock|ID2'
COLUMN type                             HEADING 'Lock|Type'

SET LINES 132 PAGES 59 FEEDBACK OFF ECHO OFF
START title132 'Processes Waiting on Locks Report'
SPOOL rep_out/&db/waiters
SELECT
     holding_session bsession_id,
     waiting_session wsession_id,
     b.username busername,
     a.username wusername,
     c.lock_type type,
     mode_held, mode_requested,
     lock_id1, lock_id2
FROM
     See code depot for full script
     sys.v_$session b,
     sys.dba_waiters c,
     sys.v_$session a
WHERE
     c.holding_session=b.sid and
     c.waiting_session=a.sid
/
SPOOL OFF
PAUSE press Enter to continue
CLEAR COLUMNS
SET LINES 80 PAGES 22 FEEDBACK ON
TTITLE OFF

In the script in Source 11.35, the lock_id1 and lock_id2 columns map into the object upon which the lock is being held. An example of the report in Source 11.35 output is shown in Listing 11.30.

LISTING 11.30 Example waiters report output.

Date: 10/14/01                                                                                                                       Page:   1
Time: 05:11 PM                     Processes Waiting on Locks Report                                        SYS
                                                          galinux1 database 

  Holding   Waiting Holding    Waiting    Lock            Mode       Mode          Lock    Lock
      SID       SID           User       User       Type            Held       Requested      ID1     ID2
--------- --------- ---------- ---------- --------------- ---------- ---------- ------- -------- --------
          7        14   DBAUTIL  SYSTEM  Transaction Exclusive  Exclusive    65580     279

press Enter to continue

Monitoring Sessions Causing Blocked Locks

Again, the catblock.sql script must be run in order to create the dba_blockers view. The dba_blockers view indicates all sessions that are currently causing blocks that aren't blocked themselves. Source 11.35 looks at the other side of the coin: it reports on the sessions that are causing blocks by joining against v$session and dba_locks. Example output from Source 11.35 is shown in Listing 11.31.

SOURCE 11.36 Example of script to generate a report of sessions causing blocks.

rem NAME: blockers.sql
rem FUNCTION: Show all processes causing a dead lock
rem HISTORY: MRA 1/15/96 Created
rem          MRA 5/21/99 dba_locks becomes dba_lock in 8.1.5
rem          MRA 10/14/01 Verified for oracle9i
rem
COLUMN username         FORMAT a10      HEADING 'Holding|User'
COLUMN session_id                       HEADING 'SID'
COLUMN mode_held        FORMAT a10      HEADING 'Mode|Held'
COLUMN mode_requested   FORMAT a10      HEADING 'Mode|Requested'
COLUMN lock_id1         FORMAT a10      HEADING 'Lock|ID1'
COLUMN lock_id2         FORMAT a10      HEADING 'Lock|ID2'
COLUMN type                             HEADING 'Lock|Type'
SET LINES 132 PAGES 59 FEEDBACK OFF ECHO OFF
START title132 'Sessions Blocking Other Sessions Report'
SPOOL rep_out\&db\blockers
SELECT
     a.session_id, username,type,mode_held,mode_requested,
     lock_id1,lock_id2
FROM

     See code depot for full script
     sys.v_$session b,
     sys.dba_blockers c,
     sys.dba_lock a
WHERE
     c.holding_session=a.session_id AND
     c.holding_session=b.sid
/
SPOOL OFF
PAUSE press Enter to continue
CLEAR COLUMNS
SET LINES 80 PAGES 22 FEEDBACK ON

LISTING 11.31 Example blockers report.

Date: 10/14/01                                             Page:   1
Time: 05:16 PM     Sessions Blocking Other Sessions Report       SYS
                             galinux1 database 

          Holding    Lock       Mode       Mode       Lock       Lock
      SID User       Type       Held       Requested  ID1        ID2
--------- ---------- ---------- ---------- ---------- ---------- -----
        7 DBAUTIL    USER       Row-S (SS) None       31299      0
        7 DBAUTIL    USER       Exclusive  None       65580      279

This is an excerpt from Mike Ault, bestselling author of "Oracle 10g Grid and Real Application Clusters".