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.
@?/rdbms/admin/utllockt.sql
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
(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
selects.inst_id,
s.sid,
s.serial#,
p.spid,
s.username,
s.program
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