Use gv$session to tell if a query is hanging

Question

I have a query running in Oracle, which may or may not be hung. It's been running for ~10 hours now, but based on the amount of data I'm loading that may not be unreasonable.

I was looking at the session in gv$session and was wondering if there's a way to translate that information to see if there's actually any activity going on, or if the query is stuck waiting for a lock or otherwise hung.

I've already read the documentation for this view here. I'm mostly looking for tips from anyone whose had experience debugging these types of issues in Oracle.

Thanks!

Answer 1

In gv$session, the event column tells you what wait event your session is currently waiting on. If your session is waiting on some sort of lock held by another session, the event will tell you that (for example, it will be "enq: TX - row lock contention" if you are enqueued waiting to lock a row held by another session) and blocking_instance and blocking_session will be populated with the instance and session ID of the holder of the lock. You can also look at seconds_in_wait (if wait_time=0) to determine how many seconds the session has spent in the current wait event. That should at least tell you whether your session is currently "stuck" but it doesn't tell you if your query is ever really going to finish-- if there is a bad plan, it's entirely possible that you've got "good" wait events like waits for disk I/O that indicate the session is doing something but that the query is never really going to finish.

Answer 2

Based on some further research and Ollie's comment I came up with these queries that help debug the issue:

select s.sid, 
       s.username,
       s.machine,
       s.osuser, 
       cpu_time,
       (elapsed_time/1000000)/60 as minutes,
       sql_text
from gv$sqlarea a, gv$session s
where s.sql_id = a.sql_id
and s.machine like '####';


select lo.*, 
       a.sql_text
from gv$sqlarea a, gv$session_longops lo
where lo.sql_id = a.sql_id
and lo.sid = ####
order by lo.start_time;