Monitoring Blocking Sessions

from http://www.dbaref.com/monitoring-blocking-sessions

 

Many times we see the alert from OEM "Database instance PRODDB1 Warning Session 501 blocking 86 other sessions Feb 6, 2012 11:34:01 AM EST etc............". We have been asked to look at it. Now lets analyse this problem.

 

 

SQL> SELECT  BLOCKING_SESSION,  SID,  SERIAL#,   WAIT_CLASS,   SECONDS_IN_WAIT
FROM  V$SESSION WHERE BLOCKING_SESSION ='VALID' ORDER BY BLOCKING_SESSION;

BLOCKING_SESSION        SID    SERIAL# WAIT_CLASS           SECONDS_IN_WAIT
----------------   ---------              ---------- --------------------                         --------
            501                       432    65432 Idle                                  78

Here we can see that session 501 is blocking session 432 and has been for 78 seconds. We would then want to find out who is running session 501, and go find them and see why they are having a problem.

 

SQL>DESC V$SESSION;
SQL> DESC DBA_WAITERS;
SQL>DESC DBA_BLOCKERS; 
DBA_BLOCKERS displays a session if it is not waiting for a locked object but is holding a lock on an object for which another session is waiting.
SQL>DESC V$LOCK;
SQL>DESC V$LOCK_HOLDERS;
SQL>SELECT * FROM V$LOCK;

There are only three types of user locks, TX, TM and UL. 

1. UL is a user-defined lock -- a lock defined with the DBMS_LOCK package.
2. The TX lock is a row transaction lock; it's acquired once for every transaction that changes data, no matter how many objects you change in that transaction. The ID1 and ID2 columns point to the rollback segment and transaction table entries for that transaction.
3. The TM lock is a DML lock. It's acquired once for each object that's being changed. The ID1 column identifies the object being modified.

SQL> select s1.username || '@' || s1.machine || ' ( SID=' || s1.sid || ' )  is blocking ' || s2.username

|| '@' || s2.machine || ' ( SID=' || s2.sid || ' ) ' AS blocking_status 
from v$lock l1, v$session s1, v$lock l2, v$session s2
where s1.sid=l1.sid and s2.sid=l2.sid
and l1.BLOCK=1 and l2.request > 0
and l1.id1 = l2.id1
and l2.id2 = l2.id2 ;To identify the locked objects

SQL> select object_name from dba_objects where object_id=76542 ;

-- Object_id will be the ID1 column V$LOCK table where TY column value is TM.

To identify the locked row

SQL> select row_wait_obj#, row_wait_file#, row_wait_block#, row_wait_row# 
from v$session where sid=7645 ;


SQL> select do.object_name,
row_wait_obj#, row_wait_file#, row_wait_block#, row_wait_row#,
dbms_rowid.rowid_create ( 1, ROW_WAIT_OBJ#, ROW_WAIT_FILE#, ROW_WAIT_BLOCK#, ROW_WAIT_ROW# )
from v$session s, dba_objects do
where sid=3424
and s.ROW_WAIT_OBJ# = do.OBJECT_ID ;

Once we have the object_name and rowid from previous query we can just run SQL

SQL>Select * from <object_name> where rowid=<dbms_rowid.rowid_create>;

=================================================================================================
# 3 steps to kill blocking sessions

select process,sid, blocking_session from v$session where blocking_session is not null;
select SERIAL# from v$session where SID=<SID number>
alter system kill session ‘SID,SERIAL#’; 
=================================================================================================

FIND LOCKED TABLES AND KILL SESSION LOCKING TABLES FROM UNIX

select

oracle_username || ' (' || s.osuser || ')' username , s.sid || ',' || s.serial# sess_id  ,  owner || '.' || object_name object ,  object_type ,decode( l.block ,0, 'Not Blocking',1, 'Blocking', 2, 'Global') status , decode(v.locked_mode,0, 'None' , 1, 'Null' ,2, 'Row-S (SS)'  , 3, 'Row-X (SX)' , 4, 'Share',5, 'S/Row-X (SSX)' , 6, 'Exclusive', TO_CHAR(lmode)) mode_held  
from       v$locked_object v   ,  dba_objects d   ,  v$lock l   ,  v$session s  
where      v.object_id = d.object_id  
and        v.object_id = l.id1  
and        v.session_id = s.sid  
and OBJECT_name  in ( 'TQUEUEDSANTERAINCCDR','TQUEUEDSANTERACDR')
order by oracle_username   ,  session_id ;

select   p.username   pu , s.username su, s.status  stat, s.sid  ssid,   s.serial# sser ,  substr(p.spid,1,8)   spid, substr(sa.sql_text,1,2000) txt
from     v$process p ,  v$session s,  v$sqlarea sa where    p.addr = s.paddr and  s.username  is not null and s.sql_address = sa.address(+)  and  s.sql_hash_value = sa.hash_value(+)
and s.sid in (select s.sid ifrom
from       v$locked_object v   ,  dba_objects d   ,  v$lock l   ,  v$session s  
where      v.object_id = d.object_id  
and        v.object_id = l.id1  
and        v.session_id = s.sid  
and OBJECT_name  in ( 'TQUEUEDSANTERAINCCDR','TQUEUEDSANTERACDR')
)
 order by 1,2,7  ;

FROM UNIX

$ps -ef|grep spid
$kill -9 spid

=================================================================================================

SQL> @$ORACLE_HOME/rdbms/admin/utllockt.sql
drop table lock_holders
           *
ERROR at line 1:
ORA-00942: table or view does not exist



Table created.

drop   table dba_locks_temp
             *
ERROR at line 1:
ORA-00942: table or view does not exist



Table created.


0 rows created.


Commit complete.


Table dropped.


0 rows created.


Commit complete.


no rows selected


Table dropped.

SQL> ^C