Sessions

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.