Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Oracle session hangs

Re: Oracle session hangs

From: Justin Ziegler <ziegler_w_justin_at_yahoo.fr>
Date: 7 Jan 2004 14:03:04 -0800
Message-ID: <37feadaa.0401071403.65ac7e6d@posting.google.com>


Hi Ana,
Thanks for your interest. We have been working on it, but the problem remains.

Yes, the database is in archive log mode, but that is not causing the problem. A lot of other sessions are still going fine doing many inserts and updates.

I shall look in the v$sess_io, however, I doubt it'll show the problem...

I looked in v$session_wait but with no success. Nothing seems wrong, actually my transaction is visible in there, but the wait event that is shown does not seem relevant, and is not the same every time.

Nothing relevant to be seen in v$lock :-(. My session does have Three locks, but none are blocking anybody else...

I shall take a look at dba_waiters. I haven't yet looked at that one :-)

Thanks,
Justin.

"Ana C. Dent" <anacedent_at_hotmail.com> wrote in message news:<ETVJb.50279$BQ5.21668_at_fed1read03>...
> Justin Ziegler wrote:
> > Hi,
> >
> > I have spent a while searching the web and news groups to find
> > relevant information concerning this problem, but with no success so
> > far. Here it is :
> >
> > I am using
> > - Oracle 8.1.7.4 + recommended patches at time of installation which
> > was last jully.
> > - Linux RedHat Enterprise 2.1 with kernel 2.4.9-e.27enterprise #1 SMP
> > + recommended patches at time of installation last jully
> > - The machine is a big and powerfull dual Xeon, losts of RAM, lost of
> > disk
> > - Oracle is dealing with many requests coming in through 8 application
> > servers. DB is big : approx 200GB.
> >
> > For some time now, it seems that about zero to 5 sessions hang every
> > day, but seem to be doing NOTHING ! Or in other words seem to be
> > waiting for something that never comes. During this wait, the
> > corresponding oracle shadow processes are not using any cpu. However,
> > the client is waiting for some extra response from oracle.
> >
> > "Alter system kill session" does not have any effect on these
> > sessions. After a short period of time, it returns indicating that the
> > session has been marked for kill. After the "kill session" the unix
> > session process is still around, and not using any cpu. The killed
> > session shadow process stays around many hours, until the client
> > disconnects. Also the current transaction is very small. Rollback
> > would be unnoticed.
> >
> > It seems the only way to get rid of this session is to perform a unix
> > "kill -9" on the oracle shadow process. In that case, the result seems
> > relatively clean, and oracle seems to manage to clean up instantly.
> >
> > I looked into v$session / v$session_wait / v$session_event / v$lock
> > and v$locked_object and v$transaction to try and understand what these
> > sessions are doing. It seems that they are not waiting for a lock, and
> > thus are not in any sort of deadlock. It seems that a SQL DML
> > statement is active : either an insert, an update, or even sometimes a
> > select statement. The only strange info I found in there is the
> > wait_time equal to -1 :
> >
> > START_TIME SEQ# WAIT_TIME SECONDS_IN_WAIT STATE
> > EVENT
> > ----------------- ---------- ---------- ---------------
> > ------------------- ------------------------------
> > 01/03/04 22:48:41 58542 -1 4192 WAITED SHORT
> > TIME db file sequential read
> >
> > The "db file sequential read" always seems to be what we are waiting
> > for, but is not relevant for inserts for instance... Also looking in
> > the session_event view one can see that the time waiting for "db file
> > sequential read" does not increase after repeated queries :
> >
> > START_TIME SID EVENT
> > TIME_WAITED
> > ----------------- ---------- ------------------------------
> > -----------
> > 01/03/04 22:48:41 172 db file scattered read
> > 0
> > 01/03/04 22:48:41 172 file open
> > 0
> > 01/03/04 22:48:41 172 buffer busy waits
> > 2
> > 01/03/04 22:48:41 172 SQL*Net more data from client
> > 12
> > 01/03/04 22:48:41 172 SQL*Net message to client
> > 13
> > 01/03/04 22:48:41 172 latch free
> > 21
> > 01/03/04 22:48:41 172 SQL*Net more data to client
> > 41
> > 01/03/04 22:48:41 172 log file sync
> > 49
> > 01/03/04 22:48:41 172 single-task message
> > 547
> > 01/03/04 22:48:41 172 db file sequential read
> > 6126
> > 01/03/04 22:48:41 172 SQL*Net message from client
> > 176879
> >
> >
> > I have not found any relevant messages in the alert.log file.
> >
> > It seems that the sql statement that is blocked concerns a large table
> > (more than 1 million lines). Also it seems that triggers are involved
> > (except when it is a select statement). The blocked sql statement is
> > either executed by a trigger or would itself launch a trigger.
> > Sometimes, the blocked sql statement is inserting in a materialised
> > view log. However, I am not sure this large table & trigger issue is
> > relevant because most of our business activity takes place on these
> > large tables, two of which have triggers...
> >
> > I would be very greatfull for any indications or ideas... I myself do
> > not have access to metalink. Would be great if somebody could give it
> > a try.
> >
> > Thanks,
> > Justin
>
>
> My first thought that the DB is in archive redo logfile mode
> and the destination directory/volume was full, but...
> > I have not found any relevant messages in the alert.log file.
>
> Either some session is very, very busy (and has some critical resourse)
> This can be observed by repeatedly doing
> SELECT * FROM V$SESS_IO
> Look for SIDs where have large and changing values.
>
> Or some session has some critical resource and is waiting.
> You might want to try one or more of the following SQL
> statements which might provide additional clues.
> Please let me know which, if any, of these help to isolate
> the root cause of the problem.
>
>
>
> select sw.SID, ss.serial#, sw.seconds_in_wait, sw.event, username, program,
> module, action, client_info
> from v$session_wait sw , v$session ss
> where seconds_in_wait > 15
> and event <> 'SQL*Net message from client'
> and event <> 'SQL*Net more data to client'
> and event <> 'Null event'
> and event <> 'pipe get'
> and event <> 'rdbms ipc message'
> and event <> 'pmon timer'
> and event <> 'smon timer'
> and state = 'WAITING'
> and ss.sid = sw.sid;
>
>
>
>
> SELECT DECODE(request,0,'Holder: ','Waiter: ') request, ss.sid sid,
> serial#, id1, id2, lmode, vl.type type
> FROM V$LOCK vl, v$session ss
> WHERE (id1, id2, vl.type) IN (SELECT id1, id2, type FROM V$LOCK
> WHERE request>0)
> AND ss.sid = vl.sid
> ORDER BY id1, request;
>
>
>
>
> select object_name, do.object_id, session_id, serial#, osuser, username,
> locked_mode , start_time, module
> from dba_objects do, v$session, v$locked_object lo, v$transaction
> where to_date(start_time,'MM/DD/YY HH24:MI:SS') < (sysdate-(2/1440))
> and locked_mode in (3,5,6)
> and session_id = sid
> and saddr = ses_addr
> and lo.object_id = do.object_id
> order by osuser;
>
>
> select s.sid, s.serial#, oracle_username, os_user_name, o.object_name,
> vl.ctime, x.start_time
> from dba_objects o
> , v$session s
> , v$locked_object vlo
> , v$lock vl
> , v$transaction x
> where o.object_id = vlo.object_id
> and o.object_id = vl.id1
> and vlo.session_id = s.sid
> and vlo.locked_mode in (3,5,6)
> and vlo.xidusn = x.xidusn
> and vlo.xidslot = x.xidslot
> and vlo.xidsqn = x.xidsqn
> and vl.block > 0
> and sysdate > to_date(x.start_time,'MM/DD/RR HH24:MI:SS')
> +2/1440;
>
>
> select holding_session , serial#, oracle_username, os_user_name,
> module, o.object_name, vl.ctime, x.start_time, sql_text
> from dba_waiters
> , v$session s
> , v$locked_object vlo
> , v$lock vl
> , v$transaction x
> , v$open_cursor oc
> , dba_objects o
> where UPPER(mode_held) = 'EXCLUSIVE'
> and o.object_id = vlo.object_id
> and o.object_id = vl.id1
> and vlo.session_id = s.sid
> and vlo.xidusn = x.xidusn
> and vlo.xidslot = x.xidslot
> and vlo.xidsqn = x.xidsqn
> and s.sid = oc.sid
> and holding_session= s.sid;
Received on Wed Jan 07 2004 - 16:03:04 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US