dabase hang on update statement

From: Joan Hsieh <joan.hsieh_at_tufts.edu>
Date: Mon, 30 Sep 2002 07:08:34 -0800
Message-ID: <F001.004DC71F.20020930070834@fatcity.com>

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). Received on Mon Sep 30 2002 - 10:08:34 CDT