Sessions
Submitted by dave on Fri, 10/03/2008 - 17:17
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;