Blocking Sessions - Locks - Explored in Details!
To find the list of blocking sessions:
=====================================
set lines 250
select a.inst_id,a.sid, a.serial#,a.machine,c.OBJECT_NAME,a.sql_id,a.BLOCKING_SESSION,a.FINAL_BLOCKING_SESSION,a.ROW_WAIT_OBJ#
from gv$session a, gv$locked_object b, dba_objects c
where b.object_id = c.object_id
and a.sid = b.session_id
and c.owner='MANI'
AND a.BLOCKING_SESSION IS NOT NULL
Order by a.sid;
set lines 250
select a.inst_id,a.sid, a.serial#,a.machine,a.sql_id,a.BLOCKING_SESSION,a.FINAL_BLOCKING_SESSION,a.ROW_WAIT_OBJ#
from gv$session a, gv$locked_object b, dba_objects c
where b.object_id = c.object_id
and a.sid = b.session_id
and c.OBJECT_NAME='T'
--and c.owner='MANI';
also sometimes while dropping Indexes
we will get error for lock not being quired..
so you can use the below query to identify the sessions holding lock on AFPO and kill them be getting confirmation from Ram
set lines 250
select a.inst_id,a.sid, a.serial#,a.machine,a.sql_id,a.BLOCKING_SESSION,a.FINAL_BLOCKING_SESSION,a.ROW_WAIT_OBJ#
from gv$session a, gv$locked_object b, dba_objects c
where b.object_id = c.object_id
and a.sid = b.session_id
and c.OBJECT_NAME='T'
--and c.owner='MANI';
Scripts to kill blocking sessions:
==================================
select 'alter system kill session ''' || a.sid || ', ' || a.serial# || ', @' || a.inst_id ||''''||' immediate;'
from gv$session a, gv$locked_object b, dba_objects c
where b.object_id = c.object_id
and a.sid = b.session_id
and c.owner='MANI'
AND a.BLOCKING_SESSION IS NOT NULL and rownum<20
Order by a.sid;
select 'alter system kill session ''' || a.sid || ', ' || a.serial# || ', @' || a.inst_id ||''''||' immediate;'
from gv$session a, gv$locked_object b, dba_objects c
where b.object_id = c.object_id
and a.sid = b.session_id
and c.owner='MANI'
AND a.BLOCKING_SESSION IS NOT NULL
Order by a.sid;
Test case:
==========
SCREEN 1:
---------
create table ramesh.t (a char(1));
insert into ramesh.t values ('z');
commit;
select * from ramesh.t where a='z' for update;
SCREEN 2:
---------
update ramesh.t set a='x' where a='z';
SCREEN 3: (To find locks on a table)
---------
SELECT TO_CHAR(sysdate, 'DD-MON-YYYY HH24:MI:SS')
|| ' User '
||s1.username
|| '@'
|| s1.machine
|| ' ( SID= '
|| s1.sid
|| ' ) with the statement: '
|| sqlt2.sql_text
||' is blocking the SQL statement on '
|| s2.username
|| '@'
|| s2.machine
|| ' ( SID='
|| s2.sid
|| ' ) blocked SQL -> '
||sqlt1.sql_text AS blocking_status
FROM v$lock l1,
v$session s1 ,
v$lock l2 ,
v$session s2 ,
v$sql sqlt1 ,
v$sql sqlt2
WHERE s1.sid =l1.sid
AND s2.sid =l2.sid
AND sqlt1.sql_id= s2.sql_id
AND sqlt2.sql_id= s1.prev_sql_id
AND l1.BLOCK =1
AND l2.request > 0
AND l1.id1 = l2.id1
AND l2.id2 = l2.id2
/
Output:
--------
BLOCKING_STATUS
----------------------------------------------------------------------------------------------------------------------
18-JAN-2020 21:26:02 User SYS@oracletest ( SID= 250 ) with the statement: select * from ramesh.t is blocking the SQL statement on SYS@oracletest ( SID=249 ) blocked SQL -> update ramesh.t set a=:"SYS_B_0" where a=:"SYS_B_1"
Problem - find blocking sessions (By Ramesh)
================================
SELECT Decode(request, 0, 'Holder: ',
'Waiter: ')
||vl.sid sess,
status,
id1,
id2,
lmode,
request,
vl.TYPE
FROM v$lock vl,
v$session vs
WHERE ( id1, id2, vl.TYPE ) IN (SELECT id1,
id2,
TYPE
FROM v$lock
WHERE request > 0)
AND vl.sid = vs.sid
ORDER BY id1,
request ;
Blocking sessions occur when one sessions holds an exclusive lock on an object and doesn't release it before another sessions wants to update the same data. This will block the second until the first one has done its work.
From the view of the user it will look like the application completely hangs while waiting for the first session to release its lock. You'll often have to identify these sessions in order to improve your application to avoid as many blocking sessions as possible.
Recipie #1 - find blocking sessions with v$session
==================================================
SELECT
s.blocking_session,
s.sid,
s.serial#,
s.seconds_in_wait
FROM
v$session s
WHERE
blocking_session IS NOT NULL
Recipie #2 - find blocking sessions using v$lock
================================================
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
Recipie #3 - blocking sessions with all available information
=============================================================
The next query prints a few more information, it let's you quickly see who's blocking who. Run this query and you can immediately call the colleague who's locking your table:
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 l1.id2 = l2.id2
Recipie #4 - identifying blocked objects
========================================
The view v$lock we've already used in the queries above exposes even more information.
If you encounter a TM lock is means that two sessions are trying to modify some data but blocking each other. Unless one sessions finished (commit or rollback), you'll never have to wait forever.
The following queries shows you all the TM locks:
=================================================
SELECT sid, id1 FROM v$lock WHERE TYPE='TM'
SID ID1
92 20127
51 20127
The ID you get from this query refers to the actual database object which can help you to identify the problem, look at the next query:
SELECT object_name FROM dba_objects WHERE object_id= 62193848;
There queries should help you to identify the cause of your blocking sessions!
select sid, sql_id
from v$session s, v$process p
where s.paddr = p.addr
and p.spid = 62193848;
select sql_text
from v$sql
where sql_id = '<sql_id from first query>';
select spid, osuser, s.username, s.program from v$process p, v$session s where p.addr=s.paddr;
Try to get session id. and SQL text in one query with:
select sid, sql_text
from v$session s, v$process p, v$sql q
where s.paddr = p.addr
and p.spid = 62193848
and s.sql_hash_value = q.hash_value
and s.sql_address = q.address;
select s.blocking_session,s.sid,s.serial#,s.seconds_in_wait from v$session s where blocking_session IS NOT NULL;
SELECT TO_CHAR(sysdate, 'DD-MON-YYYY HH24:MI:SS')
|| ' User '
||s1.username
|| '@'
|| s1.machine
|| ' ( SID= '
|| s1.sid
|| ' ) with the statement: '
|| sqlt2.sql_text
||' is blocking the SQL statement on '
|| s2.username
|| '@'
|| s2.machine
|| ' ( SID='
|| s2.sid
|| ' ) blocked SQL -> '
||sqlt1.sql_text AS blocking_status
FROM v$lock l1,
v$session s1 ,
v$lock l2 ,
v$session s2 ,
v$sql sqlt1 ,
v$sql sqlt2
WHERE s1.sid =l1.sid
AND s2.sid =l2.sid
AND sqlt1.sql_id= s2.sql_id
AND sqlt2.sql_id= s1.prev_sql_id
AND l1.BLOCK =1
AND l2.request > 0
AND l1.id1 = l2.id1
AND l2.id2 = l2.id2
/
Post a Comment: