Monitoring DML Locks

Oracle Database Tips by Donald Burleson

The other aspects of locks are the Data Definition (DDL) and Data Manipulation (DML) locks. The views DBA_DML_LOCKS and DBA_DDL_LOCKS are both created by the catblock.sql script and are used to monitor DML and DDL locks. Let's look at two scripts (Sources 11.37 and 11.38) that report on DDL and DML locks, respectively.

SOURCE 11.37 Example of script to report on Data Definition locks.

rem Name: ddl_lock.sql
rem Function: Document DDL Locks currently in use
rem History: MRA 1/15/97 Creation
rem          MRA 5/21/99 Reformat, verify for 8i
rem
COLUMN owner          FORMAT a7    HEADING 'User'
COLUMN session_id     FORMAT 9999 HEADING 'SID'
COLUMN mode_held      FORMAT a7    HEADING 'Lock|Mode|Held'
COLUMN mode_requested FORMAT a7    HEADING 'Lock|Mode|Request'
COLUMN type           FORMAT a20    HEADING 'Type|Object'
COLUMN name           FORMAT a21    HEADING 'Object|Name'
SET FEEDBACK OFF ECHO OFF PAGES 48 LINES 79
START title80 'Report on All DDL Locks Held'
SPOOL rep_out\&db\ddl_lock
SELECT
      NVL(owner,'SYS') owner, session_id,name,type,
     mode_held,    mode_requested
FROM
see code depot for full script
     sys.dba_ddl_locks
ORDER BY 1,2,3
/
SPOOL OFF
PAUSE press Enter/return to continue
CLEAR COLUMNS
SET FEEDBACK ON PAGES 22 LINES 80
TTITLE OFF

LISTING 11.32 Example of output from the DDL_LOCK report.

Date: 10/14/01 Page: 1
Time: 05:24 PM Report on All DDL Locks Held SYS galinux1 databa

                                                         Lock    Lock
              Object                Type                 Mode    Mode
User      SID Name                  Object               Held    Request
------- ----- --------------------- -------------------- ------- -----
SYS        11 DBMS_SESSION          Body                 Null    None
SYS        11 DBMS_STANDARD         Table/Procedure/Type Null    None
SYS        12 DATABASE              18                   Null    None
SYS        12 DBMS_SESSION          Table/Procedure/Type Null    None
SYS        12 DBMS_SESSION          Body                 Null    None
SYS        12 DBMS_STANDARD         Table/Procedure/Type Null    None
SYS        13 DATABASE              18                   Null    None
SYS        13 DBMS_SESSION          Table/Procedure/Type Null    None
SYS        13 DBMS_SESSION         Body                 Null    None
SYS        13 DBMS_STANDARD         Table/Procedure/Type Null    None
SYS        14 DATABASE              18                   Null    None
SYS        14 DBMS_APPLICATION_INFO Body                 Null    None
SYS        14 DBMS_APPLICATION_INFO Table/Procedure/Type Null    None
SYS        14 DBMS_SESSION          Table/Procedure/Type Null    None
SYS        14 DBMS_SESSION          Body                 Null    None
SYS        14 DBMS_STANDARD         Table/Procedure/Type Null    None
SYSTEM      8 SYSTEM                18                   Null    None
SYSTEM     11 SYSTEM                18                   Null    None
SYSTEM     12 SYSTEM                18                   Null    None
SYSTEM     13 SYSTEM                18                   Null    None
SYSTEM     14 SYSTEM                18                   Null    None

press Enter/return to continue

SOURCE 11.38 Script to report DML locks.

rem NAME: dml_lock.sql
rem FUNCTION: Document DML locks currently in use
rem HISTORY: MRA 1/15/96 Creation
rem          MRA 5/22/99 Verfied for 8i
rem          MRA 10/14/01 Updated for 9i
rem
COLUMN owner            FORMAT a8      HEADING 'User'
COLUMN session_id                       HEADING 'SID'
COLUMN mode_held        FORMAT a10      HEADING 'Mode|Held'
COLUMN mode_requested   FORMAT a10      HEADING 'Mode|Requested'
SET FEEDBACK OFF ECHO OFF PAGES 59 LINES 80
START title80 'Report on All DML Locks Held'
SPOOL rep_out\&db\dml_lock
SELECT
     NVL(owner,'SYS') owner, session_id, name,
     mode_held, mode_requested
FROM
     sys.dba_dml_locks
ORDER BY 2
/
SPOOL OFF
PAUSE press Enter to continue
CLEAR COLUMNS
SET FEEDBACK ON PAGES 22 LINES 80
TTITLE OFF

When contention is suspected, a quick look at these DDL and DML reports can tell the DBA if a session is holding a lock on the table or object involved. A word of caution is in order here, however: Because these reports contain volatile information, they are useful only for pinpoint monitoring (i.e., when there is a problem).

Monitoring Internal Locks

The last type of lock we will look at is the internal lock (see Source 11.39). Internal locks are generated by the database's internal processes. The dba_internal_locks view is created by the catblock.sql script.

SOURCE 11.39 Example of script to document internal locks currently held.

rem NAME: int_lock.sql
rem FUNCTION: Document current internal locks
rem HISTORY: MRA 1/15/96 Creation
rem
COLUMN username        FORMAT a10      HEADING 'Lock|Holder'
COLUMN session_id                      HEADING 'User|SID'
COLUMN lock_type       FORMAT a27      HEADING 'Lock Type'
COLUMN mode_held       FORMAT a10      HEADING 'Mode|Held'
COLUMN mode_requested FORMAT a10      HEADING 'Mode|Requested'
COLUMN lock_id1        FORMAT a30      HEADING 'Lock/Cursor|ID1'
COLUMN lock_id2        FORMAT a10      HEADING 'Lock|ID2'
PROMPT 'ALL is all types or modes'
ACCEPT lock PROMPT 'Enter Desired Lock Type: '
ACCEPT mode PROMPT 'Enter Lock Mode: '
SET LINES 132 PAGES 59 FEEDBACK OFF ECHO OFF VERIFY OFF
BREAK ON username
START title132 'Report on Internal Locks Mode: &mode Type: &lock'
SPOOL rep_out\&db\int_locks
SELECT
     NVL(b.username,'SYS') username,
     session_id,lock_type,mode_held,
     mode_requested,lock_id1,lock_id2
see code depot for full script
FROM
     sys.dba_lock_internal a, sys.v_$session b
WHERE
     UPPER(mode_held) like UPPER('%&mode%') OR
     UPPER('&mode')='ALL' AND
     UPPER(lock_type) like UPPER('%&lock%') OR
     UPPER(mode_held) like UPPER('%&mode%') OR
     UPPER('&mode')='ALL' AND
     UPPER('&lock')='ALL' AND
     a.session_id=b.sid
ORDER BY 1,2
/
SPOOL OFF
PAUSE press Enter to continue
SET LINES 80 PAGES 22 FEEDBACK ON VERIFY ON
CLEAR COLUMNS
CLEAR BREAKS
UNDEF LOCK
UNDEF MODE

A caution is in order here, too: The report in Source 11.39 can run to several pages in an idle instance. An excerpt from the report is shown in Listing 11.33.

LISTING 11.33 Example internal lock report output.

Date: 10/14/01
Page:   1
Time: 05:30 PM                       Report on Internal Locks Mode: ALL Type: ALL                   SYS
                                                                 galinux1 database

Lock      User                                         Mode     Mode       Lock/Cursor
Lock
Holder     SID      Lock Type                   Held     Requested ID1                            ID2
-------- ----- --------------------------- -------- ---------- ------------------------------ ----------
DBAUTIL      7 Cursor Definition Pin    Share    None       table_1_0_139_0_0_             57BFE99C

7 Cursor Definition Lock Null None table_1_0_139_0_0_ 57BFE99C

7 Cursor Definition Lock Null None SELECT ATTRIBUTE FROM V$CONT 57B96CF0

EXT WHERE NAMESPACE = 'LBAC$L

ABELS'

7 Cursor Definition Lock Null None SELECT POL#,PACKAGE FROM LBA 57B7E728

C$POL WHERE BITAND(FLAGS,1) =

1 ORDER BY PACKAGE

7 Cursor Definition Lock Null None commit 57B5ABC0

7 Cursor Definition Lock Null None SELECT POL#,PACKAGE FROM LBA 57B96EE4

C$POL WHERE BITAND(FLAGS,1) =

1 ORDER BY PACKAGE

7 Body Definition Lock Null None SYS.DBMS_SESSION 57B879E8