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: Ana C. Dent <anacedent_at_hotmail.com>
Date: Sun, 04 Jan 2004 06:59:51 -0800
Message-ID: <ETVJb.50279$BQ5.21668@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 Sun Jan 04 2004 - 08:59:51 CST

Original text of this message

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