RE: Elapsed time of active running query

From: Michael Schmitt <mschmitt_at_uchicago.edu>
Date: Fri, 31 Oct 2008 12:24:55 -0500
Message-ID: <E37E556CF8A6C44381D2DA9FC354D241D0990FD483@EVS03.ad.uchicago.edu>

Hi Wolfgang,

That is what I expected from the documentation as well. However, that is not what I am seeing. The following is what I see in the database when I have the system user run "select * from <large_table>". The LAST_CALL_ET seems to be getting constantly reset and I only catch the STATUS as 'INACTIVE' when I know the session is still doing work. The query is still running when I received the following results, so I would expect the status to show 'ACTIVE' and the LAST_CALL_ET to increase. I randomly catch the LAST_CALL_ET sneak up a few seconds, but then it still drops down to 0 while the query is running. Once the query is actually completes, then the LAST_CALL_ET starts counting time as I would expect for an inactive session.

Any ideas?

select USERNAME,LAST_CALL_ET,COMMAND,STATUS,SQL_ID from v$session where username='SYSTEM'

USERNAME                       LAST_CALL_ET    COMMAND STATUS   SQL_ID


------------------------------ ------------ ---------- -------- -------------

SYSTEM                                    0          3 INACTIVE apjk5mm7mshmx

SQL> /

USERNAME                       LAST_CALL_ET    COMMAND STATUS   SQL_ID


------------------------------ ------------ ---------- -------- -------------

SYSTEM                                    0          3 INACTIVE apjk5mm7mshmx

SQL> /

USERNAME                       LAST_CALL_ET    COMMAND STATUS   SQL_ID


------------------------------ ------------ ---------- -------- -------------

SYSTEM                                    0          3 INACTIVE apjk5mm7mshmx



________________________________

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Wolfgang Breitling Sent: Friday, October 31, 2008 9:46 AM
To: Michael Schmitt
Cc: 'ORACLE-L'
Subject: Re: Elapsed time of active running query

v$session.LAST_CALL_ET has the elapsed time of the current sql for active sessions and the elapsed time since it was last active for inactive sessions. For single sql this is what you want but if the user's session is executing many sql as part of the transaction then it doesn't tell you how long since the user kicked off the transaction.

At 03:31 PM 10/30/2008, Michael Schmitt wrote:

Hi All,

This question is for a 10.2.0.3 database

I am looking for help on writing a SQL statement that will tell me which sessions have an active running query that have run for 10 minutes or more from the users perspective. For example, if a user executes select * from table A which holds 10 million rows, I would like this script to identify their session when the users stop watch is over 10 minutes (from the time they started their query).

I was hoping last_call_et would give me this information; however it pretty much stays at 0 due to waits I guess (and status of inactive).

I need this to be for statements while they are running.

Thanks in advance

Regards

Wolfgang Breitling
Centrex Consulting Corporation
http://www.centrexcc.com<http://www.centrexcc.com/>

--
http://www.freelists.org/webpage/oracle-l

Received on Fri Oct 31 2008 - 12:24:55 CDT