Re: Oracle session hangs

From: srivenu <srivenu_at_hotmail.com>
Date: 6 Jan 2004 21:41:04 -0800
Message-ID: <1a68177.0401062141.7ca0cb89@posting.google.com>

Justin,
I think your sessions have not hanged, they are reading data. As you said that you are querying a large table, they are reading data using index scan.
To see if they are doing any work, use this scipt from another session.

col event form a30 trunc head "Event| Waiting For" col p1 form 9999999999 trunc
col p2 form 9999999999 trunc
col p3 form 999999999 trunc
col wait_time form 999 trunc head "Last|Wait|Time" col command form a6 trunc head "Command" col state form a10 trunc
col sid form 9999 trunc
select a.sid,

	 decode(command,0,'None',2,'Insert',3,'Select',
		 6,'Update',7,'Delete',10,'Drop Index',12,'Drop Table',
		 45,'Rollback',47,'PL/SQL',command) command,
	 event,p1,p2,p3,state,wait_time

from v$session_wait a,V$session b
where b.sid=a.sid
and (a.sid>10 and event not in('SQL*Net message from client',

                        'SQL*Net message to client')
or (a.sid<=10 and event not in ('rdbms ipc message','smon timer',

        'pmon timer','SQL*Net message from client'))) order by decode(event,'pipe get','A',event),p1,p2
/

if you see, the p1, p2 or p3 values changing for the session (supposedly hanged), that means that it is doing something.

If you want to see how much time, it takes to complete, use this scipt.

col sid form 9999
col start_time head "Start|Time" form a12 trunc col opname head "Operation" form a12 trunc col target head "Object" form a24 trunc
col totalwork head "Total|Work" form 999999999 trunc col Sofar head "Sofar" form 9999999999 trunc col elamin head "Elapsed|Time|(Mins)" form 999999 trunc col tre head "Time|Remain|(Mins)" form 99999 trunc

select sid,to_char(start_time,'dd-mon:hh24:mi') start_time,

	 opname,target,totalwork,sofar,(elapsed_Seconds/60) elamin,
	 time_remaining tre

from v$session_longops
where totalwork<>sofar
order by start_time
/

I would suggest you to check the PLAN of the query and tune it.

regards
Srivenu Received on Tue Jan 06 2004 - 23:41:04 CST