Query to Obtain the Session Waits in Oracle Databases

 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:

select

 distinct

 ses.ksusenum sid,

ob.kglnaobj obj_name

 ,lk.kgllkcnt lck_cnt, lk.kgllkmod lock_mode, lk.kgllkreq lock_req

 , w.state, w.event, w.seconds_in_wait

 from

 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;