Lock / Hang Analysis

When I have to analyze database lock or even hang issues, I use the following SQL statements, scripts and references:

-- all sessions involved in blocking
-- - blocked sessions
select * 
from   v$session ses
where  ses.blocking_session is not null
union
-- - blockers
select * 
from   v$session ses
where  sid in ( select blocking_session from v$session where blocking_session is not null )
;

-- find root blocker
select 'alter system kill session '''||sid||','||serial#||''' immediate;' kill_db_cmd
,      'kill -9 '||process as kill_os_cmd
,      s.*
from   v$session s
where  sid in ( 
   select blocking_session
   from   v$session
              )
and    blocking_session is null              
--and    username = 'DBUSER'
--and    osuser = 'osuser'
;

-- ddl lock information
select dlk.session_id
,      ses.serial#
,      ses.username
,      ses.schemaname
,      ses.machine
,      ses.logon_time
,      dlk.owner
,      dlk.name
,      dlk.type
,      dlk.mode_held
,      dlk.mode_requested
,      'alter system kill session '''||ses.sid||','||ses.serial#||''' immediate;'
from   dba_ddl_locks dlk
  inner join v$session ses
          on dlk.session_id = ses.sid
where (mode_held <> 'Null' or mode_requested <> 'None')
order by dlk.session_id    
;

-- library cache lock information
select distinct ses.ksusenum as sid
,      ses.ksuseser          as serial#
,      ses.ksuudlna          as username
,      ses.ksusemnm          as module
,      obj.kglnaown          as object_owner
,      obj.kglnaobj          as object_name
,      lck.kgllkcnt          as lock_count
,      lck.kgllkmod          as lock_mode
,      lck.kgllkreq          as lock_req
,      swt.state             as wait_state
,      swt.event             as wait_event
,      swt.seconds_in_wait   as sec_in_wait
from   x$kgllk lck        -- library object locks (both held and requested)
,      x$kglob obj        -- objects
,      x$ksuse ses        -- sessions
,      v$session_wait swt -- session wait
where  lck.kgllkreq > 0   -- a lock is requested
and    obj.kglhdadr = lck.kgllkhdl
and    lck.kgllkuse = ses.addr
and    swt.sid = ses.indx
order by swt.seconds_in_wait desc
;

sta ?/rdbms/admin/utllockt
--WAITING_SESSION   LOCK_TYPE         MODE_REQUESTED MODE_HELD      LOCK_ID1          LOCK_ID2
------------------- ----------------- -------------- -------------- ----------------- -----------------
--524               None
--   511            Transaction       Exclusive      Exclusive      1835044           8310

-- it might also be of interest to look into ash
-- history, query coordinator, etc
select * 
from   v$active_session_history
where  time_waited > 0
;

-- displays a row for each lock or latch that is being held, and one row for each outstanding request for a lock or latch.
--  http://download.oracle.com/docs/cd/B14117_01/server.101/b10755/statviews_2210.htm
select * 
from   dba_lock_internal
;

-- all locked objects
select oracle_username
,      os_user_name
,      session_id
,      locked_mode
,      object_name
,      object_type
from   v$locked_object lob
,      dba_objects obj
where  lob.object_id = obj.object_id
order by oracle_username
,        os_user_name
,        session_id
;

-- enqueues
select sid
,      event
,      chr(bitand(p1,-16777216)/16777215)|| chr(bitand(p1, 16711680)/65535) "type"
,      mod(p1,16) as "mode" 
,      p2
,      p3 
from   v$session_wait 
where  event like 'enq%'
; 

-- cumulated statistics on enqueues
select * 
from   v$enqueue_statistics
;


-- see who accesses an object
select *
from   v$access
where  object like 'BLA'
;

-- oradebug dump systemstate
-- - shows complete dump of all processes including locks held/requested
-- - dump created in user dump dest
-- - connect as sysdba
oradebug setmypid
oradebug unlimit
oradebug dump systemstate 266
oradebug tracefile_name

Links / References:

• MOS: How to Find which Session is Holding a Particular Library Cache Lock [ID 122793.1]
• http://orainternals.wordpress.com/2009/06/02/library-cache-lock-and-library-cache-pin-waits/
• http://www.evdbt.com/enqwaits.sql
• http://www.perfvision.com/papers/09_enqueues.ppt