Locking and blocking information and solution

1. To see the details about the specified sid
set linesize 200 select blocking_session, sid, serial#, wait_class, seconds_in_wait from gv$session where blocking_session is not null order by blocking_session ; blocking_sessionsid serial# wait_classseconds_in_wait ---------------- ---------- ---------- -------------------- -------- 148 135 61521 idle 64

Note: We found that session 148 is blocking session 135 and has been for 64 seconds.

2. Run the script provided by oracle.

3. Simple query to find the blocking session information.

select object_id,session_id,process,locked_mode from gv$locked_object where session_id in (&session_id);

  a. This will give easy readable output for locking objects.

set lines 100
set pages 500
col "lock particulars" format a100
(select username from gv$session where sid=a.sid) || '('||a.sid||')'||
' is blocking to '||
(select username from gv$session where sid=b.sid) || '('||b.sid||')' "lock particulars"
from gv$lock a, gv$lock b where a.block = 1 and b.request> 0 and a.id1 = b.id1 and a.id2 = b.id2
lock particulars
qsitbba(9) is blocking to vitvcd(7)
qsitbba(72) is blocking to vitvcd(7)
qsitbba(9) is blocking to qtnsdp(72)
4. Use below to find kill details.

Note: need to provide the sid from above query.

setlinesize 200 col username format a15 colschemaname format a15 col program format a15 select sid,serial#,username,status,schemaname,program,to_char(logon_time,'dd/mm/yyyy hh24:ss:mm') logon_time from v$session where sid in(&sid); 

5. >kill -3/9 can be run on spid provided by below query.

set linesize 100 
column spid format a10
column username format a10
column program format a45
from gv$session s
join gv$process p on p.addr = s.paddr and p.inst_id = s.inst_id
where s.sid=1950 ;
6. >To see queries running from more than 5 sec.

Note: new feature of 11g.

set linesize 200
select s.sid, serial#, s.sql_id, (sysdate-sql_exec_start)*24*60*60 secs, sql_text from v$session s, v$sqltext t
wheres.sql_id = t.sql_id
and sql_exec_start is not null and piece = 0
and (sysdate-sql_exec_start)*24*60*60 > 5 ;

7. To find the details of locking objects.

set lines 180
set pages 500
col owner format a15
col object_name format a20
col object_type format a20
col sid a8
col serial# format a50
col status format a 10
col osuser format a10
col machine format a60

select c.owner, c.object_name, c.object_type, b.sid, b.serial#, b.status, b.osuser, b.machine from v$locked_object a , v$session b, dba_objects c where b.sid = a.session_id and a.object_id = c.object_id ;

8. To find the sql being run by blocking sid

select s.sid, s.serial#, t.sql_fulltext,t.sql_id,s.sql_hash_value,t.hash_value from v$session s, v$sql t where s.sql_address = t.address and s.sql_hash_value = t.hash_value and s.sid=1950 ;

9. To find out the query information against the process which you take from unix.

select sql_text, optimizer_mode, module, action from v$sqlarea where hash_value in ( select sql_hash_value from v$session wherepaddr=( select addr from v$process where spid=&process_id ) );

10. To see the queries running from more than 5 sec

set linesize 200 select s.sid, serial#, s.sql_id, (sysdate-sql_exec_start)*24*60*60 secs, sql_text from v$session s, v$sqltext t where s.sql_id = t.sql_id and sql_exec_start is not null and piece = 0 and (sysdate-sql_exec_start)*24*60*60 > 5 ; sid serial# sql_idsecssql_text ---------- ---------- ------------- ---------- ---------------------------------------------------------------- 492 46694 a0f74y2n959cbt 12 select /*+ first_rows(1) use_nl (p t) */ path_name,path_doci