Re: dabase hang on update statement

Jared,

Glad you answer this question. Precise show the system wait event is i/o wait. That's puzzled me. First of all. I thought the job didn't commit the statement. Then precise should show it is enqueue wait, isn't it.

Joan

Jared.Still_at_radisys.com wrote:
>
> 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: 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).