RE: Elapsed time of active running query
- From: Michael Schmitt <mschmitt@xxxxxxxxxxxx>
- To: "'breitliw@xxxxxxxxxxxxx'" <breitliw@xxxxxxxxxxxxx>
- Date: Fri, 31 Oct 2008 12:24:55 -0500
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:
