Re: Active session
Date: Fri, 11 Jan 2013 08:14:37 -0000
Message-ID: <bfydnZNr0Z17VHLNnZ2dnUVZ8umdnZ2d_at_bt.com>
"vsevolod afanassiev" <vsevolod.afanassiev_at_gmail.com> wrote in message news:dac8dbb1-6e35-41dd-becb-00016f747991_at_googlegroups.com...
|I thought that to identify an active session (session currently executing
SQL statement) one looks at sessions that have V$SESSION.STATUS = 'ACTIVE'. Value in V$SESSION.LAST_CALL_ET shows how long this statement has been running.
|
| Simple test:
| - Connect to Oracle, identify SID using SELECT DISTINCT SID FROM
V$MYSTAT, let's say SID = 10.
| - Start running query SELECT * FROM LARGE_TABLE (no ORDER BY). It starts
returning rows straight away and runs for hours.
| - In another session execute SELECT STATUS,LAST_CALL_ET FROM V$SESSION
WHERE SID = 10. I always get STATUS = INACTIVE and LAST_CALL_ET = 0.
|
| - Kill the query in session 10 and re-submit it with ORDER BY clause:
SELECT * FROM LARGE_TABLE ORDER BY <column>. It won't return any rows for hours
| - Check STATUS and LAST_CALL_ET. Now I get STATUS = ACTIVE and
LAST_CALL_ET shows number of seconds since the query started running.
|
| So it appears that when rows are returned the values of STATUS and
LAST_CALL_ET get rest.
|
| Then how to identify an active session? May be through joining V$SESSION
and V$SESSION_LONGOPS WHERE TIME_REMAINING > 0?
It depends on how you want to define active.
From Oracle's perspective, a session is active if there is an active call from the client currently executing in the server.
In your first case the number of calls is the number of fetches (plus a couple) - each fetch call makes the session go active and resets the "last call time" to zero as the call arrives; between fetches (waiting on SQL*Net from client) the session is inactive.
If you want to have the average activity of a session taken as an indicator of it's being active you'll have to engineer a method of maintaining that average, perhaps looking at the call count, cpu time, and non-idle wait time.
Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com/all-postings
Author: Oracle Core (Apress 2011)
http://www.apress.com/9781430239543
Received on Fri Jan 11 2013 - 09:14:37 CET