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: Duration of Transaction

Re: Duration of Transaction

From: Mark D Powell <Mark.Powell_at_eds.com>
Date: 8 Jan 2004 16:44:39 -0800
Message-ID: <2687bb95.0401081644.661c459d@posting.google.com>


wumutek_at_yahoo.com (Vincento Harris) wrote in message news:<2fa13ee7.0401080710.1ff97cd3_at_posting.google.com>...
> > Vincento, you should be a little more specific exactly what problem
> > you are having. The following code will generate the duration (in
> > days) from the current sysdate to the transition start time. You can
> > convert this to days, hours, minutes, seconds are desired using a
> > little date math.
> >
> > SQL> l
> > 1 select sysdate - to_date(start_time,'mm/dd/yy hh24:mi:ss')
> > 2* from v$transaction
> > SQL> /
> >
> > SYSDATE-TO_DATE(START_TIME,'MM/DD/YYHH24:MI:SS')
> > ------------------------------------------------
> > .000648148
> >
> > But in general most transactions are very short and you will not even
> > see them. Are you interested only in long running processes?
> >
> > HTH -- Mark D Powell --
> >
>
> Not sure what more information this will give but since you took time
> to
> answer you deserve a response too
>
> I work on Oracle on peoplesoft (PIA) and sometimes users log out of
> the web client and the process is not terminated Resource intensive
> programs have in more than one occasion brought the database to almost
> a halt.Sometimes the users cancel because according to them the
> session seemed frozen ,but the session at the database level is still
> high on the list of resource users and still appears connected.
> I use a third party monitoring tool sometimes I get it right on ,at
> other times the users are unable to do anything then my phone rings ..
> Trying to tell how long a transaction has been going on was part of a
> plan to put in place an alarm when transactions stay on too
> long.Hopefully tracking this alongside transactions that use of a lot
> of resources may help out.
>
>
>
> Vincento

Vincento, the problem you describe is that you system suffers dead/run-away sessions. This is not an unknown problem in some environments. Daniel provided a valid response for resouce intensive sessions, but I would like to offer another approach since all runaway sessions are not resource intensive. Still a dead session that is holding locks can be a real performance killer (that is the customer becomes lock waited on a dead session and will consider the system unresponsive).

Look for active transactions in v$transaction whose v$session.status value is INACTIVE and with a large value in last_call_et (Convert to hours/minutes/seconds). Then check to see if the OS PID, v$session.process, exists on the application server. If the PID does not exist the session is a runaway and should be killed.

If you run client server then you may just have to set a limit for how long a session can remain connected and not issue commands/statements.

There is also a dead connection detection parameter in ORANET (Net8, sqlnet, ...) that may be of interest as a possible solution.

Depending on the use of connection pooling by the web server, client server connections, and use of OS features like waits on VMS mailbox messages one or all these ideas may be of use to you.

HTH -- Mark D Powell -- Received on Thu Jan 08 2004 - 18:44:39 CST

Original text of this message

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