When a statement uses the CPU, it is not waiting. There is no wait event that accounts for using the CPU for processing. People often forget this. Your session is most likely using the CPU. There is no indication in your output that your session is waiting.
If the session is currently waiting, then the value is the amount of time waited for the current wait. If the session is not in a wait, then the value is the amount of time since the start of the last wait.
If a session is active it is either waiting on some wait event or is on a CPU processing data. In V$SESSION the combination of (STATUS = 'ACTIVE' and TIME_SINCE_LAST_WAIT_MICRO = 0) tells us the session is currently waiting on some wait event. Inversely, (STATUS = 'ACTIVE' and TIME_SINCE_LAST_WAIT_MICRO > 0) tells us the session is active but not waiting on a wait event and therefore is ON CPU.
You can use following query to get some of the session wait information:
,lk.kgllkcnt lck_cnt, lk.kgllkmod lock_mode, lk.kgllkreq lock_req
, w.state, w.event, w.seconds_in_wait
x$kgllk lk, x$kglob ob,x$ksuse ses
, v$session_wait w
where lk.kgllkhdl in
(select kgllkhdl from x$kgllk where kgllkreq >0 )
and ob.kglhdadr = lk.kgllkhdl
and lk.kgllkuse = ses.addr
and w.sid = ses.indx
order by sid;
Post a Comment