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: Daniel Morgan <damorgan_at_x.washington.edu>
Date: Thu, 08 Jan 2004 08:36:37 -0800
Message-ID: <1073579718.912371@yasure>


Vincento Harris wrote:

>>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

Based on what you are saying the length of time is not the issue but rather resource utilization is. I'd suggest you look at the following:

  1. Add resource_limit=TRUE to the init.ora, bounce the database and add modify the CPU_PER_CALL in the default profile or create a new profile for that class of users causing the problem.
  2. Go to http://tahiti.oracle.com and look up DBMS_RESOURCE_MANAGER built-in package. You can find a partially built demo of it at http://www.psoug.org/reference/dbms_res_mgr.html
  3. Go to http://tahiti.oracle.com and look up DBMS_APPLICATION_INFO.SET_SESSION_LONGOPS.
The problem you are trying to solve is best solved proactively: Not reactively.
-- 
Daniel Morgan
http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)
Received on Thu Jan 08 2004 - 10:36:37 CST

Original text of this message

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