I'm afraid all of the suggestions made are useless for this problem.
We have exactly the same problem on our oracle server 8.1.7 - some sessions
are hanging (not for archive log problems or lock problems) but
simply that the session seem to have stopped in middle of performing
an insert. v$session_wait shows wait on db file read (exactly as in
Justin's case), and there are no entry in the v$transaction table for
the session. However, the session does have an active sql statement
which shows that it is in middle of performing an insert. I am
quite certain that it's stuck, as we've seen session performing
insert of 21 rows (which should take fraction of second) hanging for
days or longer. dba_blockers and dba_waiter tables show no blocked
or waiting sessions. Most likely it seems to be some sort corruption or
bug in oracle transaction/lock handling mechanism. This type of
hanging on insert only occurs when there is simultaneous deletion
occuring from another session. However, the session performing
deletion commits every 30 seconds, so it's not possible to block
for days based on the locking considerations alone. Perhaps someone
with expert-level knowledge in Oracle might have an insight into
this problem?
srivenu_at_hotmail.com (srivenu) wrote in message news:<1a68177.0401062141.7ca0cb89_at_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 13 2004 - 12:59:28 CST
