I want to find out the query for which any operation is blocked. From DBA I'm getting input that particular session is blocked but I cannot proceed with investigation further without knowing which query is causing issue. is there any way to find out exact query causing issue?
5
-
is your session blocking or is it blocked by another session?Cyrille MODIANO– Cyrille MODIANO2017-10-23 10:08:55 +00:00Commented Oct 23, 2017 at 10:08
-
Read this : oraclerecipes.com/monitoring/find-blocking-sessionsXING– XING2017-10-23 10:23:12 +00:00Commented Oct 23, 2017 at 10:23
-
Your DBA should be able to tell you who the blocker is and what query is being run by the blocker.sandman– sandman2017-10-23 10:37:13 +00:00Commented Oct 23, 2017 at 10:37
-
@CyrilleMODIANO blocked by another sessionakki2346– akki23462017-10-24 14:55:08 +00:00Commented Oct 24, 2017 at 14:55
-
@sandman sadly he just putting logs at me.akki2346– akki23462017-10-24 14:56:03 +00:00Commented Oct 24, 2017 at 14:56
Add a comment
|
4 Answers
Oracle Blocking session is very common. Please check the below query it will help you to get the detailed information.
select l1.inst_id,l1.sid, ' IS BLOCKING ', l2.sid,l1.type,l2.type,l1.lmode,l2.lmode,l2.inst_id
from gv$lock l1, gv$lock l2
where l1.block =1 and l2.request > 0and l1.id1=l2.id1
and l1.id2=l2.id2;
Comments
First get the SQL_ID of the BLOCKING session from this script:
SELECT /*+ RULE */
s.sid,
s.serial#,
p.spid "OS SID",
s.sql_hash_value "HASH VALUE",
s.username "ORA USER",
s.status,
s.osuser "OS USER",
s.machine,
s.terminal,
s.type,
s.program,
s.logon_time,
s.last_call_et,
s.sql_id,
l.id1,
l.id2,
decode(l.block,0,'WAITING',1,'BLOCKING') block,
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 ,
round(l.ctime/60,2) "MIN WAITING",
l.type
FROM v$process p, v$session s, v$Lock l
where p.addr = s.paddr
and s.sid=l.sid
and
(l.id1,l.id2,l.type) in
(SELECT l2.id1, l2.id2, l2.type
FROM V$LOCK l2
WHERE l2.request<>0)
order by l.id1,l.id2,l.block desc;
Then pass this SQL_ID into this script:
select sql_text from v$sqltext
where sql_id=<SQL_ID>;
Comments
You can use the following query to find out whichs sessions are bloking and what they do:
select s.module,
s.program,
s.machine,
s.osuser,
sql.sql_text
from v$session s,
v$sqlarea sql
where s.sql_id = sql.sql_id
and s.sid in (select blocking_session from v$session)
1 Comment
Nick Perkins
"column ambiguously defined"
/******************* Blocking Sessions: ********************/
set lines 750 pages 9999
col blocking_status for a100
select s1.inst_id,s2.inst_id,s1.username || '@' || s1.machine
|| ' ( SID=' || s1.sid || ' ) is blocking '
|| s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) ' AS blocking_status
from gv$lock l1, gv$session s1, gv$lock l2, gv$session s2
where s1.sid=l1.sid and s2.sid=l2.sid and s1.inst_id=l1.inst_id 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
order by s1.inst_id;
/******************* Locking Sessions: ********************/
SELECT l.inst_id,
SUBSTR(L.ORACLE_USERNAME,1,8) ORA_USER,
SUBSTR(L.SESSION_ID,1,3) SID,
S.serial#,
SUBSTR(O.OWNER||'.'||O.OBJECT_NAME,1,40) OBJECT, P.SPID OS_PID,
DECODE(L.LOCKED_MODE, 0,'NONE',
1,'NULL',
2,'ROW SHARE',
3,'ROW EXCLUSIVE',
4,'SHARE',
5,'SHARE ROW EXCLUSIVE',
6,'EXCLUSIVE',
NULL) LOCK_MODE
FROM sys.GV_$LOCKED_OBJECT L
, DBA_OBJECTS O
, sys.GV_$SESSION S
, sys.GV_$PROCESS P
WHERE L.OBJECT_ID = O.OBJECT_ID
and l.inst_id = s.inst_id
AND L.SESSION_ID = S.SID
and s.inst_id = p.inst_id
AND S.PADDR = P.ADDR(+)
order by l.inst_id;
