CODEWITHSUNDEEP
oracle-databaseoracle10g
Paul
Paul

Reputation: 3954

Use gv$session to tell if a query is hanging

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!

Upvotes: 9

Views: 80050

Answers (3)

Shyam D
Shyam D

Reputation: 17

This will be Helpful to Check the Current Running Session

select a.SID, a.SERIAL#, c.OBJECT_NAME 
from v$session a, v$locked_object b, user_objects c
where a.SID=b.SESSION_ID and b.OBJECT_ID=c.OBJECT_ID

Upvotes: 0

Justin Cave
Justin Cave

Reputation: 231671

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.

Upvotes: 11

Paul
Paul

Reputation: 3954

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;

Upvotes: 7

Related Questions