All Session Summary

Query

-- All Session Summary
select status, 
       count(*) 
  from v$session 
 group by rollup(status);

Example output:

STATUS	COUNT(*)

ACTIVE	23
INACTIVE	59
	82

Session Summary with Active Count

Query

-- Session Summary with Active Count
select username,
       SUM(CASE status WHEN 'ACTIVE' THEN 1 ELSE 0 END) || 
         ' active / ' || count(*) || ' total' as Active_Total_CONN
from v$session 
where username IS NOT NULL
  and username not in ('DBSNMP','SYS','SYSTEM') 
group by username order by username 

Example output:

USERNAME  ACTIVE_TOTAL_CONN

DBUSER1	  0 active / 1 total
DBUSER2	  3 active / 56 total

Sessions with Unix Process Detail

-- Sessions with Unix Process Detail
select 
       substr(a.spid,1,9) pid, 
       substr(b.sid,1,5) sid, 
       substr(b.serial#,1,5) ser#, 
       substr(b.machine,1,6) box, 
       substr(b.username,1,10) username, 
--       b.server, 
       substr(b.osuser,1,8) os_user, 
       substr(b.program,1,30) program 
from v$session b, v$process a 
 where 
b.paddr = a.addr 
and type='USER' 
order by spid; 

Session Summary with Machine Info

select username, machine, count(*)
From v$session
WHERE LENGTH(USERNAME) > 0
group by username, machine
order by count(*) desc;

Session Detail with Kill Statement

select sid, 
       serial#, 
       username, 
       status, 
       machine, 
       terminal, 
       program, 
       module, 
       logon_time, 
       last_call_et, 
       'ALTER SYSTEM KILL SESSION ''' || SID || ',' || SERIAL# || ''';' as killstmt  
from v$session;

Sniped Sessions Waiting to be Killed

What is normal: Normally there are a few sessions hanging out waiting to be cleaned up by PMON. If there are more than 10 in this list, we are probably having a lot more session churn than usual. Killing sniped sessions may help release resources faster but it looks like PMON still has the last word on releasing resources.

SELECT 'ALTER SYSTEM KILL SESSION ''' || SID || ',' || SERIAL# || ''';' as sqltext
FROM v$session
WHERE status='SNIPED';

Session Events By User

-- Session Events By User
SELECT
   username,
   machine,
   event,
   total_waits,total_timeouts,
   time_waited,average_wait
FROM
   sys.v_$session_event a,
   sys.v_$session b
WHERE
   a.sid= b.sid
ORDER BY 1,2;

Redo Blocks Generated per Session

What is normal: Because of lifetimes of sessions this is not always the most useful stat but it might give you an idea of sessions that are doing more work.

-- Redo Blocks Generated per Session
SELECT s.sid, s.serial#, s.username, s.program,

Session Wait Event Summary

-- Session Wait Event Summary
SELECT ses.username, 
       swc.SID, 
       ses.status, 
       swc.wait_class, 
       swc.total_waits, 
       ROUND ((swc.time_waited / 100), 2) time_waited_secs
  FROM SYS.v_$session_wait_class swc, 
       SYS.v_$session ses
 WHERE ses.SID = swc.SID 
   AND ses.username IS NOT NULL 
   AND swc.wait_class != 'Idle'
 ORDER BY username, SID, wait_class;

Tools

Tanel Poder's Oracle Session Snapper

http://tech.e2sn.com/oracle-scripts-and-tools/session-snapper A low impact way to observe performance counters for a session.