Active and Inactive Sessions

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

Advertisements

6 thoughts on “Active and Inactive Sessions

  1. 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

  2. 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.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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s