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!

Is there an entry in v$session_longops that you could check to see if it's progressing? Alternatively check this link for ways to see if your session is being blocked: orafaq.com/node/854 — Ollie
– Ollie, Commented Apr 27, 2012 at 14:33
Thanks, I can combine this with gv$sqlarea to see what statements are holding me up! — Paul
– Paul, Commented Apr 27, 2012 at 14:37

Justin Cave · Accepted Answer · 2012-04-27 14:56:27Z

11

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.

answered Apr 27, 2012 at 14:56

Justin Cave

233k25 gold badges378 silver badges395 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

JDPeckham Over a year ago

select 'ALTER SYSTEM KILL SESSION '''||sid||','||"SERIAL#"||''';', event from gv$session where machine = 'your machine' is handy to generate the commands to kill them off. Just copy paste column 1

Paul · Accepted Answer · 2012-04-27 14:55:47Z

7

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;

edited Apr 27, 2012 at 14:55

answered Apr 27, 2012 at 14:46

Paul

3,97413 gold badges55 silver badges89 bronze badges

1 Comment

Ollie Over a year ago

Without checking the exact columns in those views, wouldn't it be better to link gv$sqlarea to gv$session_longops via SQL_ID? I'm not in front of my PC at the moment so I can't check :-(

slavoo · Accepted Answer · 2013-12-13 06:11:30Z

0

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

edited Dec 13, 2013 at 6:11

slavoo

6,11464 gold badges40 silver badges41 bronze badges

answered Dec 13, 2013 at 5:54

Shyam D

172 bronze badges

Collectives™ on Stack Overflow

Use gv$session to tell if a query is hanging

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related