Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 
 Home
 E-mail Us
 Oracle Articles
New Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

 Applications
 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 
 

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

             7 Body Definition Lock        Null       None     LBACSYS.LBAC_CACHE             57BA1D8C

             7 Cursor Definition Lock      Null       None     SELECT MAX(TAG#)   FROM LBAC$L 57B7C038

                                                               AB

 

             7 Cursor Definition Lock      Null       None     select pol#, usr_name, usr_lab 57B91108

                                                                els, package, privs from lbac$

                                                                user_logon where usr_name = username

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


 

   
 
��  
 
 
Oracle Training at Sea
 
 
 
 
oracle dba poster
 

 
Follow us on Twitter 
 
Oracle performance tuning software 
 
Oracle Linux poster
 
 
 

 

Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


                    









Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


 

Copyright © 1996 -  2017

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.

Remote Emergency Support provided by Conversational