Oracle Database Check blocking sessions

Check blocking sessions

Let's create a scenario

/*

Create a blocking lock

To begin, create a situation where one user is actively blocking another. Open two sessions. Issue the following commands in Session 1 to build the test table:

*/

SQL> create table tstlock (foo varchar2(1), bar varchar2(1));

--Table created.

SQL> insert into tstlock values (1,'a');

--1 row created.

SQL> insert into tstlock values (2, 'b');

--1 row created.

SQL> select * from tstlock ;

FOO BAR

--- ---

1   a

2   b

--2 rows selected.

SQL> commit ;

--Commit complete.

--Now grab a lock on the whole table, still in Session 1:

SQL> select * from tstlock for update ;

--And in Session 2, try to update a row:

SQL> update tstlock set bar= 2  'a' where bar='a' ;

--This statement will hang, blocked by the lock that Session 1 is holding on the entire table.

/*Identify the blocking session

Oracle provides a view, DBA_BLOCKERS, which lists the SIDs of all blocking sessions. But this view is often, in my experience, a good bit slower than

simply querying V$LOCK, and it doesn't offer any information beyond the SIDs of any sessions that are blocking other sessions.

The V$LOCK view is faster to query, makes it easy to identify the blocking session, and has a lot more information.

*/

SQL> select * from v$lock ;

ADDR     KADDR           SID TY        ID1        ID2      LMODE    REQUEST      CTIME      BLOCK

-------- -------- ---------- -- ---------- ---------- ---------- ---------- ---------- ----------

AF9E2C4C AF9E2C60        479 TX     131078      16739          0          6        685          0

ADDF7EC8 ADDF7EE0        422 TM      88519          0          3          0        697          0

ADDF7F74 ADDF7F8C        479 TM      88519          0          3          0        685          0

ADEBEA20 ADEBEB3C        422 TX     131078      16739          6          0        697          1

/*

Note the BLOCK column. If a session holds a lock that's blocking another session, BLOCK=1. Further, you can tell which session is being blocked

by comparing the values in ID1 and ID2. The blocked session will have the same values in ID1 and ID2 as the blocking session, and,

since it is requesting a lock it's unable to get, it will have REQUEST > 0

*/

--In the query above, we can see that SID 422 is blocking SID 479. SID 422 corresponds to Session 1 in our example, and SID 479 is our blocked Session 2.

--To avoid having to stare at the table and cross-compare ID1's and ID2's, put this in a query:

SQL> select l1.sid, ' IS BLOCKING ', l2.sid

  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

SQL> /

       SID 'ISBLOCKING'         SID

---------- ------------- ----------

       422  IS BLOCKING         479

--1 row selected.

--Even better, if we throw a little v$session into the mix, the results are highly readable:

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 ;

BLOCKING_STATUS

----------------------------------------------------------------------------------------------------

BULKLOAD@yttrium ( SID=422 )  is blocking BULKLOAD@yttrium ( SID=479 )

--1 row selected

-- Check what queries are running in these SID

SELECT a.sid,a.serial#,b.sql_text FROM

    v$session a,v$sqlarea b

WHERE a.sql_address = b.address;

--specific SID

SELECT a.sid,a.serial#,b.sql_text

FROM

    v$session a,v$sqlarea b

WHERE a.sql_address = b.address   AND   a.sid = '42';

--Check blocking sessions using v$session

SELECT

   s.blocking_session,

   s.sid,

   s.serial#,

   s.seconds_in_wait

FROM

   v$session s

WHERE

   blocking_session IS NOT NULL;

   

-- Identifying blocked objects

SELECT sid, id1 FROM v$lock WHERE TYPE='TM';

SELECT object_name FROM dba_objects WHERE object_id=79159;

-- get deailed information bt instance

SELECT 'Instance '||s1.INST_ID||' '|| s1.username || '@' || s1.machine

   || ' ( SID=' || s1.sid || ','|| s1.serial#||s1.status||  '  )  is blocking '

   || s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) ' ||s2.sql_id

    FROM gv$lock l1, gv$session s1, gv$lock l2, gv$session s2

   WHERE s1.sid=l1.sid AND

    s1.inst_id=l1.inst_id AND

    s2.sid=l2.sid AND

    s2.inst_id=l2.inst_id AND

    l1.BLOCK=1 AND

   l2.request > 0 AND

   l1.id1 = l2.id1 AND

   l2.id2 = l2.id2 ;

--check sql for the SID

select sql_id from v$session where sid=42;

select sql_fulltext from v$sql where sql_id ='01zffbm7nxg6c';

/*Killing Oracle Sessions

Be very careful when identifying the session to be killed. If you kill a session belonging to a background process you will cause an instance crash.

There are a number of ways to kill blocking sessions both from Oracle sql prompt and externally.

Identify the Session to be Killed

ALTER SYSTEM KILL SESSION

ALTER SYSTEM DISCONNECT SESSION

The Windows Approach

The UNIX Approach

*/

-- Find the serial for blocking session SID

select SERIAL# from v$session where SID=39; -- result 130

--Final step to kill the blocking session alter system kill session ‘SID,SERIAL#’;

SQL> alter system kill session ‘39,130’;

--System altered.

SQL> ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE;

--This does not affect the work performed by the command, but it returns control back to the current session immediately, rather than waiting for confirmation of the kill.

--In a RAC environment, you optionally specify the INST_ID, shown when querying the GV$SESSION view. This allows you to kill a session on different RAC node.

SQL> ALTER SYSTEM KILL SESSION 'sid,serial#,@inst_id';

SQL> ALTER SYSTEM DISCONNECT SESSION 'SID,SERIAL#' IMMEDIATE;

--The DISCONNECT SESSION command kills the dedicated server process, which is equivalent to killing the server process from the operating system

kill -9 spid