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: [EMAIL PROTECTED] >To: Multiple recipients of list ORACLE-L ><[EMAIL PROTECTED]> >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" > > > <[EMAIL PROTECTED] To: > Multiple recipients of list ORACLE-L ><[EMAIL PROTECTED]> > > cc: > > > Sent by: >Subject: RE: how to find elapsed time for a query >in oracle 8.1.7 Database > [EMAIL PROTECTED] > > > > > > > > > 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: [EMAIL PROTECTED] > >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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] > >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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
