Re: dabase hang on update statement

From: <Jared.Still_at_radisys.com>
Date: Mon, 30 Sep 2002 09:13:31 -0800
Message-ID: <F001.004DC946.20020930091331@fatcity.com>

Joan,

You may be able to diagnose this when it happens again by logging in as SYS using svrmgrl.

Run the following SQL statement if the logon was successful:

select

   s.username username,
   e.event event,
   s.sid,
   e.p1text,
   e.p1,
   e.p2text,
   e.p2,
   e.wait_time,
   e.seconds_in_wait,
   e.state

from v$session s, v$session_wait e
where s.username is not null

and s.sid = e.sid
order by s.username, upper(e.event)
/

This may indicate a wait on an internal resource. It won't tell you why it's waiting, but you'll know which area to look at.

Jared

Joan Hsieh <joan.hsieh_at_tufts.edu>
Sent by: root_at_fatcity.com
09/30/2002 08:08 AM
Please respond to ORACLE-L

        To:     Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
        cc: 
        Subject:        dabase hang on update statement

Hi list,

oracle 8.1.7.2
os AIX 4.3.3

Occasionally we had hang situation on our Financial production database. The batch job supposed to finish within 2 hours at 3:30 am. But sometimes it just hang there and never finished the process. We have to kill the job in oracle session and os level. Since oracle just marked killed status and takes long time to clean the resource. We have precise tool to check all the activities within that time being. The problem statement is a update sql.
I summarized the db activity here;
at 3:14 am, there were two similar update statement against same big table. The first on shown a big cpu consumption but finished at 3:30am. The second started around same time shown big cpu use at beginning and hang there forever until we killed it at 9:00 am. The database shown big i/0 wait on the statement. My question is why i/0 wait? It just hang the whole database and didn't do any thing. Do you have any ideas? I am not sure the statement was commited or not.

Thanks,

Joan

UPDATE PS_PAYMENT_TBL  SET         CANCEL_ACTION = 'P'  WHERE  PYMNT_ID
= :1  AND        POST_STATUS_AP = 'P'  AND        CANCEL_ACTION IN

('R','H','C')

UPDATE PS_PAYMENT_TBL  SET         POST_STATUS_AP =
'P',                  IN_PROCESS_FLG = 'N'  WHERE  PROCESS_INSTANCE =

:1 AND PYMNT_ID = :2 AND IN_PROCESS_FLG = 'Y'
--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Joan Hsieh
INET: joan.hsieh_at_tufts.edu

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).

--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author:
INET: Jared.Still_at_radisys.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------