RE: Elapsed time of active running query

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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] 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/>

Other related posts: