Oracle Tips and Tricks — David Fitzjarrell

May 28, 2008

Let My Data Go!

Filed under: General — dfitzjarrell @ 13:22

I’ve written here regarding Oracle locks but apparently the post didn’t explain enough about the views involved. Let’s fix that.

Two views are probably the most beneficial when investigating locks:

V$LOCK
V$LOCKED_OBJECT

with V$LOCK providing an overall view of the active locks in a database and V$LOCKED_OBJECT providing detail on who has TM (DML) locks against which database objects.

Looking at V$LOCK to see what information is available we see that this view provides:

 the lock address (KADDR)
 the SID of the session holding the lock (SID)
 the type of lock (TYPE) (including two additional columns [ID1,ID2] which
                          can further identify the lock)
 the lock mode (LMODE)
 the requested mode (REQUEST)
 the time since the current mode was granted (CTIME)
 whether or not the lock is blocking another session (BLOCK)

The TYPE column describes whether this is a user or a system lock. The three user-type locks are

TM — DML enqueue [INSERT/UPDATE/DELETE transactions]
TX — Transaction enqueue [possibly DDL locks on an object]
UL — User supplied/defined [created by the DBMS_LOCK package]

System locks are usually held for a short duration, although there are exceptions, such as the MR and AE lock types. These lock types are

AE        Application Edition locks  (11g Edition Based Redefinition****)
BL        Buffer hash table instance
CF        Control file schema global enqueue
CI        Cross-instance function invocation instance (RAC, OPS, single-instance PQ*)
CU        Cursor bind*****
DF        datafile instance
DL        Direct loader parallel index create
DM        Mount/startup db primary/secondary instance
DR        Distributed recovery process
DX        Distributed transaction** entry
FS        File set
HW        Space management operations on a specific segment
IN        Instance number
IR        Instance recovery serialization global enqueue
IS        Instance state
IV        Library cache invalidation instance
JQ        Job queue
KK        Thread kick
LA .. LP  Library cache lock instance lock (A..P = namespace)
MM        Mount definition global enqueue
MR        Media recovery
NA..NZ    Library cache pin instance (A..Z = namespace)
PF        Password File
PI, PS    Parallel operation
PR        Process startup*******
QA..QZ    Row cache instance (A..Z = cache)
RT        Redo thread global enqueue
SC        System change number instance
SM        SMON
SN        Sequence number instance
SQ        Sequence number enqueue
SS        Sort segment
ST        Space transaction enqueue
SV        Sequence number value
TA        Generic enqueue
TS        New block allocation enqueue (ID2=1)
TS        Temporary segment enqueue (ID2=0)
TT        Temporary table enqueue
UN        User name
US        Undo segment DDL***
WL        Being-written redo log enqueue******

[The SM lock for SMON is acquired when the process ‘wakes up’ to check for work it has to do; the lock is taken as exclusive then converted to shared when the check is complete. This happens every time SMON becomes active, which is usually every five minutes. The lock is only active for a short period of time (one, maybe two seconds on average) and usually is not one worthy of concern.]

* The CI enqueue is used not only in RAC and OPS configurations but also by the parallel query coordinator. As an example before a direct read operation the reader process uses a CI call to DBWR to request a checkpoint of all dirty blocks in the cache belonging to the segment about to be read.

** The DX lock is set when single-master or multi-master replication is configured and active or when transactions span a database link, such as inserts and updates to a local table using data from a remote site. The DR lock is set when recovering from failed transactions involving two-phase commits (usually found in environments where replication as described at the beginning of this paragraph is used).

*** The US lock is set to serialize DDL (or processes(+) that invoke DDL) against UNDO segments, specifically:

CREATE ROLLBACK SEGMENT
DROP ROLLBACK SEGMENT
ALTER ROLLBACK SEGMENT ONLINE
ALTER ROLLBACK OFFLINE
ALTER ROLLBACK SEGMENT SHRINK
ALTER ROLLBACK SEGMENT STORAGE
Offlining PENDING OFFLINE RBS by SMON
SMON – abortive offline cleanup
(+)STARTUP

There is nothing to ‘fix’ with this lock as Oracle is managing the UNDO segments and preventing any access to them until any DDL or process in the above list has completed.

**** The AE lock is created for each user session connected to the database provided edition-based redefinition is enabled/installed. Each user session has one AE lock in shared mode, waiting to be pressed into service using the “ALTER SESSION SET EDITION=<edition_name>;” statement. This presumes the default database edition has been set by the DBA using “ALTER DATABASE DEFAULT EDITION = <edition_name>;”. Editions allow developers to change database object definitions in a private environment while the application is still in use, reserving the changes until the edition is put into service at the database level. More information can be found here.

***** The CU lock protects the bind variable definitions during cursor optimization. These locks tend to be used when cursor_sharing is set to a value other than EXACT (but the behaviour is version-dependent). In 8i they are allocated one lock per bind, and in 9i and later releases it appears they are allocated one lock per cursor. These locks should not be a performance problem as they are not held for long periods of time (the bind and optimization steps usually are fast operations, even in systems experiencing high levels of traffic).

****** The WL lock protects the current redo log from concurrent writes since redo logs are written sequentially. This lock may be seen frequently but it should not be a long-standing lock nor should it impede performance. Of course the size of your redo logs and the frequency in which they switch can impact performance especially when running in NOARCHIVELOG mode which may make the WL locks more visible.

******* The PR lock protects memory structures while a process starts and may not be visible in V$LOCK unless an issue arises which prevents a process from successfully starting. PR locks held for a considerable amount of time will need to be investigated as to which process is holding the lock and what is preventing the process from completing its startup. The alert log is the best place to begin such an investigation.

The ID1 and ID2 columns provide varying information from the OBJECT_ID for the object involved (for TM locks — ID1) to additional information differentiating multiple levels of a given lock type (as in the TS lock listed above). Since the ID1 column can contain information other than the id of the locked object it’s better to use the V$LOCKED_OBJECT view to investigate who has which database objects locked.

In the case of UL locks ID1 reports the lock handle used to generate and manage the lock through the DBMS_LOCK package:

SQL> select * From v$lock;

ADDR     KADDR           SID TY        ID1        ID2      LMODE    REQUEST      CTIME      BLOCK
-------- -------- ---------- -- ---------- ---------- ---------- ---------- ---------- ----------
2EC34228 2EC34254        160 XR          4          0          1          0      17231          0
2EC3429C 2EC342C8        160 CF          0          0          2          0      17228          0
2EC34384 2EC343B0        160 RS         25          1          2          0      17223          0
2EC343F8 2EC34424        162 PW          1          0          3          0      17219          0
2EC3446C 2EC34498        161 RT          1          0          6          0      17223          0
2EC344E0 2EC3450C        170 AE         99          0          4          0      17120          0
2EC34554 2EC34580        128 AE         99          0          4          0        131          0
2EC345C8 2EC345F4        146 AE         99          0          4          0      17209          0
2EC3463C 2EC34668        162 MR          1          0          4          0      17223          0
2EC346B0 2EC346DC        162 MR          2          0          4          0      17223          0
2EC34724 2EC34750        162 MR          3          0          4          0      17223          0

ADDR     KADDR           SID TY        ID1        ID2      LMODE    REQUEST      CTIME      BLOCK
-------- -------- ---------- -- ---------- ---------- ---------- ---------- ---------- ----------
2EC34798 2EC347C4        162 MR          4          0          4          0      17223          0
2EC3480C 2EC34838        162 MR          5          0          4          0      17223          0
2EC34880 2EC348AC        162 MR        201          0          4          0      17223          0
2EC348F4 2EC34920        155 AE         99          0          4          0      17120          0
2EC34968 2EC34994        159 TS          3          1          3          0      17218          0
2EC349DC 2EC34A08        157 AE         99          0          4          0      17209          0
2EC34A50 2EC34A7C        128 UL 1073742010          0          6          0        128          0
2EC34AC4 2EC34AF0        139 AE         99          0          4          0      17120          0
2EC34B38 2EC34B64        136 AE         99          0          4          0      17113          0
2EC34BAC 2EC34BD8        140 AE         99          0          4          0      16985          0
2EC34C20 2EC34C4C        137 AE         99          0          4          0      16978          0

ADDR     KADDR           SID TY        ID1        ID2      LMODE    REQUEST      CTIME      BLOCK
-------- -------- ---------- -- ---------- ---------- ---------- ---------- ---------- ----------
2EC34C94 2EC34CC0        129 AE         99          0          4          0        106          0

23 rows selected.

SQL>

In the case of RS (reclaimable space) and TS (temporary space) locks ID2 provides the lock level; there may be other lock types that populate the ID2 column with non-zero values.

As noted in the list the CTIME column lists the time since the current mode was granted; as expected if the lock mode changes then the CTIME counter resets and starts counting from that point forward until the next mode change.

V$LOCKED_OBJECT provides the following for all DML locks (type TM) in the database:

 the undo segment number (XIDUSN)
 the slot number (XIDSLOT)
 the sequence number (XIDSQN)
 the object identifier (OBJECT_ID)
 the session identifier (SESSION_ID)
 the username (ORACLE_USERNAME)
 the O/S user (OS_USER_NAME)
 the O/S process id (PROCESS)
 the mode (LOCKED_MODE)

The slot and sequence number apply to the undo segment assigned to each lock and the locked mode is a number spanning the range from 0 through 6, identified as follows:

0 — no lock held
1 — NULL
2 — Row share lock (SS)
3 — Row exclusive (SX)
4 — Shared (S)
5 — S/Row-exclusive (SSX)
6 — Exclusive (X)

The above values apply to both the V$LOCK and V$LOCKED_OBJECT views.

How, then, can these views be used to report who has which objects locked and in what modes? The following query can be run:

set linesize 200 pagesize 60 trimspool on
column username format a10
column osuser format a10
column sid format 99999
column object format a35
column type format a35

select s.username, s.sid, s.serial#,
       s.osuser, k.ctime, o.object_name object, k.kaddr,
case l.locked_mode when 1 then 'No Lock'
                   when 2 then 'Row Share'
                   when 3 then 'Row Exclusive'
                   when 4 then 'Shared Table'
                   when 5 then 'Shared Row Exclusive'
                   when 6 then 'Exclusive'
end locked_mode,
case
when k.type = 'AE' then 'Application Edition'
when k.type = 'BL' then 'Buffer Cache Management (PCM lock)'
when k.type = 'CF' then 'Controlfile Transaction'
when k.type = 'CI' then 'Cross Instance Call'
when k.type = 'CU' then 'Bind Enqueue'
when k.type = 'DF' then 'Data File'
when k.type = 'DL' then 'Direct Loader'
when k.type = 'DM' then 'Database Mount'
when k.type = 'DR' then 'Distributed Recovery'
when k.type = 'DX' then 'Distributed Transaction'
when k.type = 'FS' then 'File Set'
when k.type = 'IN' then 'Instance Number'
when k.type = 'IR' then 'Instance Recovery'
when k.type = 'IS' then 'Instance State'
when k.type = 'IV' then 'Library Cache Invalidation'
when k.type = 'JQ' then 'Job Queue'
when k.type = 'KK' then 'Redo Log Kick'
when k.type like 'L%' then 'Library Cache Lock'
when k.type = 'MM' then 'Mount Definition'
when k.type = 'MR' then 'Media Recovery'
when k.type like 'N%' then 'Library Cache Pin'
when k.type = 'PF' then 'Password File'
when k.type = 'PI' then 'Parallel Slaves'
when k.type = 'PR' then 'Process Startup'
when k.type = 'PS' then 'Parallel slave Synchronization'
when k.type like 'Q%' then 'Row Cache Lock'
when k.type = 'RT' then 'Redo Thread'
when k.type = 'SC' then 'System Commit number'
when k.type = 'SM' then 'SMON synchronization'
when k.type = 'SN' then 'Sequence Number'
when k.type = 'SQ' then 'Sequence Enqueue'
when k.type = 'SR' then 'Synchronous Replication'
when k.type = 'SS' then 'Sort Segment'
when k.type = 'ST' then 'Space Management Transaction'
when k.type = 'SV' then 'Sequence Number Value'
when k.type = 'TA' then 'Transaction Recovery'
when k.type = 'TM' then 'DML Enqueue'
when k.type = 'TS' then 'Table Space (or Temporary Segment)'
when k.type = 'TT' then 'Temporary Table'
when k.type = 'TX' then 'Transaction'
when k.type = 'UL' then 'User-defined Locks'
when k.type = 'UN' then 'User Name'
when k.type = 'US' then 'Undo segment Serialization'
when k.type = 'WL' then 'Writing redo Log'
when k.type = 'XA' then 'Instance Attribute Lock'
when k.type = 'XI' then 'Instance Registration Lock'
 end type
from v$session s, sys.v_$_lock c, sys.v_$locked_object l, dba_objects o, sys.v_$lock k
where o.object_id = l.object_id
and l.session_id = s.sid
and k.sid = s.sid
and s.saddr = c.saddr
and k.kaddr = c.kaddr
and k.lmode = l.locked_mode
and k.lmode = c.lmode
and k.request = c.request
order by object;

This query will run on 9iR2 and later releases — earlier versions of Oracle require DECODE statements and I’ll leave it to you to make any changes for Oracle 8.1.7 and earlier releases.

Sessions holding locks blocking other sessions can be reported as well:

with blocked as (
 select sid blocked, serial#, username, blocking_session
 from v$session
 where blocking_session is not null
),
blocking as (
 select sid blocking, serial# bl_serial#, username bl_username
 from v$session
),
obj_info as (
 select l.session_id, o.object_name, l.object_id,
 decode(l.locked_mode,   1, 'No Lock',
        2, 'Row Share',
        3, 'Row Exclusive',
        4, 'Shared Table',
        5, 'Shared Row Exclusive',
        6, 'Exclusive') locked_mode
 from v$locked_object l, dba_objects o
 where o.object_id = l.object_id
)
select blocked, serial#, username, blocking, bl_serial#, bl_username, session_id, object_name, object_id, locked_mode
from blocked, blocking, obj_info
where blocking = blocking_session
and session_id = blocking_session;

Again, this runs on 9iR2 and later releases of Oracle which provide the subquery factoring syntax (the WITH clause).

Oracle provides another script to output a ‘tree’ structure of the locks held in a database, found in $ORACLE_HOME/rdbms/admin, named utllockt.sql. This particular script reports the locks which are blocking other sessions. It uses the CONNECT BY syntax to produce a tree-like output; an example is shown in the internal documentation in the script.

So what to do should you find a blocking lock? That depends on the type of application you’re running. It may be as simple as contacting the user with the blocking lock and asking him or her to commit or rollback the changes he or she has made. Or, if they’ve done the unthinkable, assuming the session was hung, and clicked on that little red x to kill the application, you may need to simply kill the session which remains. There is no ‘cut and dried’, ‘one-size-fits-all’ solution.

So, locks aren’t the mystery some might think, and investigating them (with these and other scripts/queries available on the web) isn’t a thankless, convoluted task. Investigation does take some patience, however, as it may require the output from several scripts to determine where the problems lie. I’ve provided some starting points and I suggest you ‘google’ for additional scripts other DBAs find useful. Hopefully I’ve provided enough information to point you in the right direction so you won’t be like the Scarecrow in L. Frank Baum’s “The Wizard Of Oz” and not know which way is up. Or down.

Now, go investigate those locks.

Advertisements

1 Comment »

  1. Reblogged this on to share what I love and commented:
    Beautifully written about Oracle DB Session Locks,
    also check http://www.oracle-base.com/articles/misc/killing-oracle-sessions.php

    Comment by guidedmissile — November 25, 2013 @ 02:08 | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.

%d bloggers like this: