Finding query from oracle which is blocking session

Question

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?

Answer 1

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;

Answer 2

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>;

Answer 3

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)