Most people are aware of the STATUS column in V$SESSION. If it’s ‘ACTIVE’ then that connection is in the process of consuming database resources (running a SQL statement etc).
However, a lesser known column which is probably even more useful is the LAST_CALL_ET column. The name suggests the time since the last call, but the documentation provides a more useful description:
If the session STATUS is currently ACTIVE, then the value represents the elapsed time (in seconds) since the session has become active.
If the session STATUS is currently INACTIVE, then the value represents the elapsed time (in seconds) since the session has become inactive.
Thus this is a great column to get a view on what sessions are doing on your system, for example
select s.sid||','||s.serial# sess, s.USERNAME, s.last_call_et, s.status, s.sql_address, s.program from v$session s where ( s.status = 'ACTIVE' and s.last_call_et > 10 ) or -- has been active for 10 seconds or more ( s.status != 'ACTIVE' and s.last_call_et > 1200 ); -- has been inactive for 20 mins or more
Hi! I have interesting select that allows to find nonsystem long active sessions on all nodes of cluster with command to kill them. Hope it can be useful.
select t2.sql_text, sess.inst_id, sess.username, sess.sid, sess.serial#, sess.last_call_et, sess.wait_class, sess.event, sess.machine, sess.program, sess.sql_id, sess.kill_command from
(select t1.inst_id, t1.username, t1.sid, t1.serial#, t1.last_call_et, t1.wait_class, t1.event, t1.machine, t1.program, t1.sql_id, ‘alter system kill session ”’ ||to_char(t1.SID)||’,’||to_char(t1.SERIAL#)||’,@’||to_char(t1.inst_id)||”’ immediate;’ kill_command
from gv$session t1
where t1.status=’ACTIVE’ and t1.last_call_et>300 and username is not null) sess,
where sess.SQL_ID=t2.SQL_ID and sess.inst_id=t2.inst_id
group by t2.sql_text, sess.inst_id, sess.username, sess.sid, sess.serial#, sess.last_call_et, sess.wait_class, sess.event, sess.machine, sess.program, sess.sql_id, sess.kill_command
Thanks for the contribution.
Excellent, thanks for this. It’s going into my stash!
One minor comment:
with SQL+ (yes, I *still* use it) the semi-colon followed by the “–” is a no-no.
Nice catch, I added the comments in as an afterthought on the post. Here’s my complete version of a basic (single instance) session viewer SQL, where last_call_et is just shown in brackets for each session
select s.sid, s.serial#,
|| ‘ (‘||s.last_call_et||’)’ username,
case when s.lockwait is null then
case when s.username is null then
case when s.program is not null then
( case when lower(s.program) like ‘oracle%(%)%’ then regexp_substr(lower(s.program),’^oracle.*\((.*)\).*$’,1, 1, ‘i’, 1)
when s.username is null then ( select p.program
from v$process p
where s.PADDR = p.ADDR )
end || ‘-‘ || s.osuser program ,
when blocking_session is null then cast(null as varchar2(1))
cast(( select substr(s1.osuser||’-‘||s1.program,1,60)
from v$session s1
where s1.sid = s.blocking_session
) as varchar2(60))
from v$session s,
( select sid job_sid
where type = ‘JQ’ ) j
where s.sid = j.job_sid(+)
order by s.sid;
My FAVORITIST view 🙂
Thank you Connor ! excellent.