T-SQL: Session Locks, Blocks & Waits

Find all locking and blocking on a database

SELECT    lpad(' ',DECODE(request,0,0,1))||sid||'('||inst_id||')' sess, 
        id1, id2, lmode, request, type, ctime, block
FROM gV$LOCK
WHERE (inst_id,id1) IN (SELECT inst_id,id1 FROM gV$LOCK WHERE lmode = 0)
ORDER BY id1,request

Query to Drill down access locks per spid / schema / Object name

SELECT /*+ RULE */
       lk.SID, se.username, se.osuser, se.machine,
       DECODE (lk.TYPE,
               'TX', 'Transaction',
               'TM', 'DML',
               'UL', 'PL/SQL User Lock',
               lk.TYPE
              ) lock_type,
       DECODE (lk.lmode,
               0, 'None',
               1, 'Null',
               2, 'Row-S (SS)',
               3, 'Row-X (SX)',
               4, 'Share',
               5, 'S/Row-X (SSX)',
               6, 'Exclusive',
               TO_CHAR (lk.lmode)
              ) mode_held,
       DECODE (lk.request,
               0, 'None',
               1, 'Null',
               2, 'Row-S (SS)',
               3, 'Row-X (SX)',
               4, 'Share',
               5, 'S/Row-X (SSX)',
               6, 'Exclusive',
               TO_CHAR (lk.request)
              ) mode_requested,
       TO_CHAR (lk.id1) lock_id1, TO_CHAR (lk.id2) lock_id2, ob.owner,
       ob.object_type, ob.object_name,
       DECODE (lk.BLOCK, 0, 'No', 1, 'Yes', 2, 'Global') BLOCK, 
       se.lockwait
  FROM v$lock lk, dba_objects ob, v$session se
 WHERE lk.TYPE IN ('TM', 'UL')
   AND lk.SID = se.SID
   AND lk.id1 = ob.object_id(+)
   AND se.username = 'SCHEMA_NAME';
   --AND (lk.SID = '452');
   --and ob.object_name = 'ACCESSORY';

Show all tables which have any access lock mode on them:

SELECT b.inst_id,
       b.session_id AS sid,
       NVL(b.oracle_username, '(oracle)') AS username,
       a.owner AS object_owner,
       a.object_name,
       Decode(b.locked_mode, 0, 'None',
                             1, 'Null (NULL)',
                             2, 'Row-S (SS)',
                             3, 'Row-X (SX)',
                             4, 'Share (S)',
                             5, 'S/Row-X (SSX)',
                             6, 'Exclusive (X)',
                             b.locked_mode) locked_mode,
       b.os_user_name
FROM   dba_objects a,
       gv$locked_object b
WHERE  a.object_id = b.object_id
ORDER BY 1, 2, 3, 4;

View all Wait Class Descriptions

SELECT name, wait_class FROM V$EVENT_NAME ORDER BY name; I

Bug 7234778 – Unnecessary "cursor: pin S wait on X" waits [ID 7234778.8]

HOW TO FIND BLOCKING SESSION FOR MUTEX WAIT EVENT cursor: pin S wait on X [ID 786507.1]

SELECT p2raw 
,to_number(substr(to_char(rawtohex(p2raw)), 1, 8), 'XXXXXXXX') sid 
FROM v$session 
WHERE event = 'cursor: pin S wait on X'

Show waits relating to Library Cache Pin

--Run as SYS
SELECT sid, event, p1raw, seconds_in_wait, wait_time
FROM sys.gv_$session_wait
WHERE event = 'library cache pin'
AND state = 'WAITING';

Prioritise session waits by the session waiting the longest

--prioritizes session waits by the session waiting the longest
SELECT sid,
       username,
       event,
       total_waits,
       100 * round((total_waits / sum_waits),2) pct_of_total_waits,
       time_wait_sec,
       total_timeouts,
       average_wait_sec,
       max_wait_sec
FROM
(SELECT a.event, 
       b.sid sid,
       decode (b.username,null,c.name,b.username) username,
       a.total_waits total_waits,
       round((a.time_waited / 100),2) time_wait_sec,
       a.total_timeouts total_timeouts,
       round((average_wait / 100),2)
       average_wait_sec,
       round((a.max_wait / 100),2) max_wait_sec
  FROM sys.v_$session_event a, 
       sys.v_$session b,
       sys.v_$bgprocess c,
       sys.v_$process d
 WHERE a.event NOT IN
          ('lock element cleanup',
          'pmon timer',
          'rdbms ipc message',
          'smon timer',
          'SQL*Net message from client',
          'SQL*Net break/reset to client',
          'SQL*Net message to client',
          'SQL*Net more data from client',
          'dispatcher timer',
          'Null event',
          'parallel query dequeue wait',
          'parallel query idle wait - Slaves',
          'pipe get',
          'PL/SQL lock timer',
          'slave wait',
          'virtual circuit status',
          'WMON goes to sleep'
          )
   AND a.event NOT LIKE 'DFS%'
   AND a.event NOT LIKE 'KXFX%'
   AND a.sid = b.sid
   AND d.addr = b.paddr 
   AND c.paddr (+) = b.paddr
   and b.username is not NULL  
),
(select sum(total_waits) sum_waits
 FROM sys.v_$session_event a, 
       sys.v_$session b
 WHERE a.event NOT IN
          ('lock element cleanup',
          'pmon timer',
          'rdbms ipc message',
          'smon timer',
          'SQL*Net message from client',
          'SQL*Net break/reset to client',
          'SQL*Net more data from client',
          'SQL*Net message to client',
          'dispatcher timer',
          'Null event',
          'parallel query dequeue wait',
          'parallel query idle wait - Slaves',
          'pipe get',
          'PL/SQL lock timer',
          'slave wait',
          'virtual circuit status',
          'WMON goes to sleep'
          )
   AND a.event NOT LIKE 'DFS%'
   AND a.event NOT LIKE 'KXFX%'
   and b.username is not NULL
   AND a.sid = b.sid)
order by 6 desc, 1 asc

Prioritises wait by their wait time and filters out most ‘idle’ waits

--prioritizes waits by their wait time and filters out most "idle" waits
select event,
       total_waits,
       round(100 * (total_waits / sum_waits),2) pct_waits,
       time_wait_sec,
       round(100 * (time_wait_sec / greatest(sum_time_waited,1)),2)
       pct_time_waited,
       total_timeouts,
       round(100 * (total_timeouts / greatest(sum_timeouts,1)),2) 
       pct_timeouts,
       average_wait_sec
from
(select event,
       total_waits,
       round((time_waited / 100),2) time_wait_sec,
       total_timeouts,
       round((average_wait / 100),2) average_wait_sec
from sys.v_$system_event
where event not in 
('lock element cleanup', 
 'pmon timer', 
 'rdbms ipc message',
 'rdbms ipc reply',
 'smon timer', 
 'SQL*Net message from client', 
 'SQL*Net break/reset to client',
 'SQL*Net message to client',
 'SQL*Net more data from client',
 'dispatcher timer',
 'Null event',
 'parallel query dequeue wait',
 'parallel query idle wait - Slaves',
 'pipe get',
 'PL/SQL lock timer',
 'slave wait',
 'virtual circuit status',
 'WMON goes to sleep',
 'jobq slave wait',
 'Queue Monitor Wait',
 'wakeup time manager',
 'PX Idle Wait') AND 
 event not like 'DFS%' AND 
 event not like 'KXFX%'),
(select sum(total_waits) sum_waits,
        sum(total_timeouts) sum_timeouts,
        sum(round((time_waited / 100),2)) sum_time_waited
 from sys.v_$system_event
 where event not in 
 ('lock element cleanup', 
 'pmon timer', 
 'rdbms ipc message',
 'rdbms ipc reply',
 'smon timer', 
 'SQL*Net message from client', 
 'SQL*Net break/reset to client',
 'SQL*Net message to client',
 'SQL*Net more data from client',
 'dispatcher timer',
 'Null event',
 'parallel query dequeue wait',
 'parallel query idle wait - Slaves',
 'pipe get',
 'PL/SQL lock timer',
 'slave wait',
 'virtual circuit status',
 'WMON goes to sleep',
 'jobq slave wait',
 'Queue Monitor Wait',
 'wakeup time manager',
 'PX Idle Wait') AND 
 event not like 'DFS%' AND 
 event not like 'KXFX%')
order by 4 desc, 1 asc
Advertisements

One Response to T-SQL: Session Locks, Blocks & Waits

  1. Pingback: Investigating High Churn in Oracle « Mohsin's DBA Blog

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

%d bloggers like this: