Block? Lock?

Database is SLOW? My query is running for so long time?

Lets check Blocks first

Simplest Query

SET LINES 157 PAGES 300

select blocking_instance,blocking_session,sid,inst_id,last_call_et,status, wait_class, seconds_in_wait,state
from gv$session where blocking_session is not null order by 1,2;
col description for a50
select sid,
decode(state, ‘WAITING’,’Waiting’,’Working’) state,
decode(state,’WAITING’, ‘So far ‘||seconds_in_wait, ‘Last waited ‘|| wait_time/100)|| ‘ secs for ‘||event “Description”
from v$session
where username = ‘&a’;

 

set long 100000
set pages 300 lines 157
select s.blocking_instance,s.blocking_session,s.sid,s.inst_id,s.last_call_et,s.status, s.wait_class, s.seconds_in_wait,s.state,sql_text
from gv$session s, gv$sqlarea sa
where s.blocking_session is not null
and s.sql_id=sa.sql_id
and s.sid=&a
order by 1,2;

———- Session not getting killed after killing it  ———-

select vt.addr, xidusn, xidslot, xidsqn,used_ublk
from v$transaction vt, v$lock vl
where vt.addr=vl.addr
and vt.addr='<TADDR from V$SESSION>’

Find out the SPID of the session first.

select sid, spid,pid, osuser, s.program from v$process p, v$session s where p.addr=s.paddr and s.sid=<>;

Now crash the session

oradebug setospid <SPID>
oradebug event immediate crash;

Arup Nanda Blog

http://www.oracle.com/technetwork/issue-archive/2012/12-jul/o42dba-1566567.html

 

Views which can save life

V$SESSTAT, V$SYSSTAT