Active and Inactive Sessions

April 13, 2016

1–2 minutes

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

10 responses to “Active and Inactive Sessions”

Oleg

April 13, 2016 at 12:36 pm

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,
gv$sql t2
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

Reply
1. connormcdonald
  
  April 13, 2016 at 2:36 pm
  
  Hi Oleg,
  
  Thanks for the contribution.
  
  Reply
Noons

April 14, 2016 at 9:09 am

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.

Reply
1. connormcdonald
  
  April 14, 2016 at 10:11 am
  
  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.username
  || ‘ (‘||s.last_call_et||’)’ username,
  case when s.lockwait is null then
  case when s.username is null then
  nvl2(j.job_sid,’ACTIVE’,’INACTIVE’)
  else
  s.status
  end
  else ‘BLOCKED’
  end status,
  s.sql_id sql_id,
  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)
  else s.program
  end )
  when s.username is null then ( select p.program
  from v$process p
  where s.PADDR = p.ADDR )
  end || ‘-‘ || s.osuser program ,
  blocking_session,
  case
  when blocking_session is null then cast(null as varchar2(1))
  else
  cast(( select substr(s1.osuser||’-‘||s1.program,1,60)
  from v$session s1
  where s1.sid = s.blocking_session
  ) as varchar2(60))
  end blocker
  from v$session s,
  ( select sid job_sid
  from v$lock
  where type = ‘JQ’ ) j
  where s.sid = j.job_sid(+)
  order by s.sid;
  
  Cheers,
  Connor
  
  Reply
Jeff Smith

April 15, 2016 at 6:07 am

My FAVORITIST view 🙂

Reply
Go Self Service or Go Home | Oracle Business Intelligence

May 9, 2016 at 1:21 am

[…] 12. Active and Inactive Sessions […]

Reply
Sanjay

May 1, 2017 at 5:10 pm

Thank you Connor ! excellent.

Reply
2017–what grabbed your attention | Learning is not a spectator sport

December 19, 2017 at 9:28 am

[…] Active and Inactive Sessions […]

Reply
2018-what grabbed your attention – Learning is not a spectator sport

January 7, 2019 at 10:06 pm

[…] Active and Inactive Sessions […]

Reply
2019-what grabbed your attention – Learning is not a spectator sport

December 9, 2019 at 8:06 pm

[…] Active and Inactive Sessions […]

Reply