Use gv$session to tell if a query is hanging
oracle, oracle10g
Solution
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.
Problem
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!