RE: RE: how to find elapsed time for a query in oracle 8.1.7 Database

LAST_CALL_ET means the elapsed time since the last call. If the session is inactive the idle time is therefore LAST_CALL_ET minus the (unknown) time taken to process this call. I have just checked on a 8.1.7 database an ACTIVE session for which LAST_CALL_ET was increasing each time I was running my query. Which leads me to think that if STATUS is 'ACTIVE' and LAST_CALL_ET is greater than 1200, it means that SQL_ADDRESS and SQL_HASH_VALUE have a fair chance to point to a statement which has run for more than 20mn.

>----- ------- Original Message ------- -----
>From: kranti.pushkarna_at_accenture.com
>To: Multiple recipients of list ORACLE-L
><ORACLE-L_at_fatcity.com>
>Sent: Fri, 07 Mar 2003 03:58:38
>
>
>Stephane,
> LAST_CALL_ET lets u know that user is idle
>for so long (correct me
>id i am wrong) and I want to know which queries are
>taking long time.
>Unfortunately I cannot use Oracle trace for it.
>Regards,
>Kranti Pushkarna
>
>
>
>
>
>
> "Stephane Faroult"
>
>
> <sfaroult_at_oriolecorp.com To:
> Multiple recipients of list ORACLE-L
><ORACLE-L_at_fatcity.com>
> > cc:
>
>
> Sent by:
>Subject: RE: how to find elapsed time for a query
>in oracle 8.1.7 Database
> root_at_fatcity.com
>
>
>
>
>
>
>
>
> 03/07/2003 04:28 PM
>
>
> Please respond to
>
>
> ORACLE-L
>
>
>
>
>
>
>
>
>
>
>
>
>>Hi Gurus,
>> Is there any way to find out which queries
>>are taking say more than
>>20 min in oracle 8.1.7 Database.
>>Regards,
>>Kranti Pushkarna
>>
>
>Switch your mobile phone on :-).
>
>Unless you put your database in trace mode, it is
>fairly difficult to get
>this information. Something which comes to my mind
>but is not very good is
>to have a small program which queries V$SESSION and
>gets max(last_call_et)
>for all the ACTIVE sessions, and checks again (20 *
>60 - the value
>precedently found) seconds later.
>
>Regards,
>
>Stephane Faroult
>Oriole
>--
>Please see the official ORACLE-L FAQ:
>http://www.orafaq.net
>--
>Author: Stephane Faroult
> INET: sfaroult_at_oriolecorp.com
>
>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).
>
>
>
>
>
>
>This message is for the designated recipient only
>and may contain
>privileged, proprietary, or otherwise private
>information. If you have
>received it in error, please notify the sender
>immediately and delete the
>original. Any other use of the email by you is
>prohibited.
>
>--
>Please see the official ORACLE-L FAQ:
>http://www.orafaq.net
>--
>Author:
> INET: kranti.pushkarna_at_accenture.com
>
>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).
>---------------------------------------------------
>------------------
>---------------------------------------------------
>------------------

Regards,

Stephane Faroult
Oriole

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Stephane Faroult
  INET: sfaroult_at_oriolecorp.com

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