Active session

From: vsevolod afanassiev <vsevolod.afanassiev_at_gmail.com>
Date: Thu, 10 Jan 2013 13:32:17 -0800 (PST)
Message-ID: <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? Received on Thu Jan 10 2013 - 22:32:17 CET