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