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

 
 Home
 E-mail Us
 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 Locks and Latches

Oracle Tips by Burleson Consulting

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".


 

   
 
��  
 
 

 
 
 
 
oracle dba poster
 

 
 
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 -  2014

All rights reserved by Burleson

Oracle ? is the registered trademark of Oracle Corporation.