Oracle Veritabanı: Bloke Eden Sessionların Bulunması - Blocking Sessions

Sessionlar birbirini ancak içlerinden biri bir object üzerinde exclusive lock oluşturduğunda ve diğer session extensive lock oluşan o object ile ilgili update,delete veya insert tarzı bir işlem yapmak istediğinde bloklarlar. Bu durumda ilk session işini bitirene kadar diğeri ilk session’ı bekler.

İlk aşama bloke eden sessionları bulmaktır. Bloke eden sessionları bulmak için aşağıda 4 tane farklı yol belirttim. Herhangi birisi kullanılabilinir.

1-

 SELECT   
 s.blocking_session,   
 s.sid,   
 s.serial#,   
 s.seconds_in_wait   
 FROM   
 v$session s   
 WHERE   
 blocking_session IS NOT NULL;  

2- Aşağıdaki 2 tablo da çok basit bir şekilde hangi sessionların wait ettiği ve kimin yüzünden wait ettiği görülebilir.

 select * from dba_blockers;  
   
   
 Select * from dba_waiters;  

3- 

 SELECT   
 l1.sid || ' is blocking ' || l2.sid blocking_sessions  
 FROM   
 v$lock l1, v$lock l2  
 WHERE  
 l1.block = 1 AND  
 l2.request > 0 AND  
 l1.id1 = l2.id1 AND  
 l1.id2 = l2.id2

4-

 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 ;  

Hangi Object'in Bloklandığı Nasıl Bulunabilinir?

Bloke edilen session bulunduğunda direk ne yapıldığını aşağıdaki sorguyla bulabiliriz.

  select status, event, sql_id from v$session;   
 select sql_text from v$sql where sql_id = ;   

Burada çıkan sorguyla bloke eden session'ın son sql'ini bulabiliriz; ancak bu sql istediğimiz sql olmayabilir; çünkü bloke eden session işini bitirdikten sonra başka sorgular çalıştırmış olabilir. Bunun için bloke edilen object'i bulmalıyız.

v$lock tablosu sorgulandığında sid ve id1 adlı kolonlar alınır. Bu kolonlar dba_objects adlı tablodaki objectlerle eşleştirilirler. Buradan hangi object üzerinde bir kilit olduğunu çıkarabiliriz.

 SELECT sid, id1 FROM v$lock;  
   
 SELECT object_name FROM dba_objects WHERE object_id='object_id'  
   

Sonrasında yukarıda bulduğumuz object_name değerini aşağıdaki sorgumuzda where koşuluna ekleyebiliriz.

 select *  
 from v$sql_plan   
 where id = 1   
 and operation in ('UPDATE','DELETE')  
 --and object_name='object_adı';  

Buradan da  hangi sql'in yukarıda bulduğu object üzerinde Update veya Delete işlemini yaptığını görebiliriz.

select sql_id,timestamp,object_owner,object_name   
   
 from v$sql_plan   
   
 where   
   
 id = 1   
   
 and   
   
 operation in ('UPDATE','DELETE');   

(Yukarıdaki sorgununu ayrı bir versiyonu)



Sql'leri İncelemek için Hangi Tablolara Bakabilirim?

Aşağıdaki tablolar kullanılan sql'leri  incelemek için iyi kaynaklardır.

 select * from v$open_cursor;   
   
 select * from v$sqlarea;   
   
 select * from v$sql;   
   
 select * from v$sql_plan;   

Bloke eden sql her şekilde bulunabilinir. Yeter ki blocking session id'imiz olsun. Session_id ye göre v$lock'tan bloke olmuş object, sonrasında da v$sql_plan'den de o object üzerindeki belirli operasyonları inceleyebiliriz.


Session'lara göre  sorgu tarihçesi

 select a.SAMPLE_TIME, a.SQL_OPNAME, a.SQL_EXEC_START, a.program, a.client_id, b.SQL_TEXT  
 from DBA_HIST_ACTIVE_SESS_HISTORY a, dba_hist_sqltext b --v$sqltext b  
 where a.SQL_ID = b.SQL_ID   
 --and a.session_id=5801  
 order by a.SQL_EXEC_START desc;  

Genel İşlem Sırası:

Lock'lar, kilitlenmiş session'larla ilgili bilgil edinmek veya kilidi bulmak ve bunlarla ilgilenmek için işlem sırası:
1- İlk olarak uygun tablolar kontrol edilir. (v$lock,v$session,dba_blockers,dba_waiters) Buralardan session_id alınmaya çalışılır.
2- Serial# elde edililir. Bu bilgi session'ı kill etmek için kullanılır.
3-Session kill edilir.


Appendix:

Aşağıdaki script Oracle'ın sayfasında bulduğum bir  script'tir. Bu scriptle çıkarılan raporda lock yaratan session'lar ve bunların kill edilmesiyle ilgili komutlar yer almaktadır. Bir scriptin içerisine yazılaraktan çalıştırılabilinir.

SET ECHO off   
 REM NAME:  TFSLKILL.SQL   
 REM USAGE:"@path/tfslkill"   
 REM ------------------------------------------------------------------------   
 REM REQUIREMENTS:   
 REM  SELECT on V$LOCK, V$SESSION, SYS.USER$, SYS.OBJ$   
 REM ------------------------------------------------------------------------   
 REM PURPOSE:   
 REM  The report generated by this script gives information on sessions   
 REM  which are holding locks. It also provides the information necessary   
 REM  to terminate the session using the ALTER SYSTEM KILL SESSION command.    
 REM ------------------------------------------------------------------------   
 REM Main text of script follows:   
    
 set linesize 132 pagesize 66   
 break on Kill on username on terminal   
 column Kill heading 'Kill String' format a13   
 column res heading 'Resource Type' format 999   
 column id1 format 9999990   
 column id2 format 9999990   
 column lmode heading 'Lock Held' format a20   
 column request heading 'Lock Requested' format a20   
 column serial# format 99999   
 column username format a10 heading "Username"   
 column terminal heading Term format a6   
 column tab format a35 heading "Table Name"   
 column owner format a9   
 column Address format a18   
 column ctime heading "Seconds"  
 select     nvl(S.USERNAME,'Internal') username,   
      nvl(S.TERMINAL,'None') terminal,   
      L.SID||','||S.SERIAL# Kill,   
      U1.NAME||'.'||substr(T1.NAME,1,20) tab,   
      decode(L.LMODE,1,'No Lock',   
           2,'Row Share',   
           3,'Row Exclusive',   
           4,'Share',   
           5,'Share Row Exclusive',   
           6,'Exclusive',null) lmode,   
      decode(L.REQUEST,1,'No Lock',   
           2,'Row Share',   
           3,'Row Exclusive',   
           4,'Share',   
           5,'Share Row Exclusive',   
           6,'Exclusive',null) request,  
     l.ctime  
 from     V$LOCK L,   
      V$SESSION S,   
      SYS.USER$ U1,   
      SYS.OBJ$ T1   
 where     L.SID = S.SID   
 and     T1.OBJ# = decode(L.ID2,0,L.ID1,L.ID2)   
 and     U1.USER# = T1.OWNER#   
 and     S.TYPE != 'BACKGROUND'   
 order by 1,2,5   
 /   

Referans:
1-http://www.oraclerecipes.com/monitoring/find-blocking-sessions/
2-http://waseemslearning.blogspot.com.tr/2011/05/find-locks-or-identifying-sessions.html
3-Appendix'deki Lock Script'i: Display Blocking Locks (Also Provides SID and Serial# of the Session) (Doc ID 1020007.6)