Archive for August, 2012

Session State

August 1, 2012 Leave a comment

#1 Get information on the sessions waiting and working

Query for displaying sessions, session state, and events:

select sid,
decode(state, ‘WAITING’,’Waiting’,
‘Working’) state,
‘So far ‘||seconds_in_wait,
‘Last waited ‘||
‘ secs for ‘||event
from v$session
where username = ‘TEST’;


556 Waiting So far 610498 secs for SQL*Net message from client

#2 Sessions from a specific user

select SID, osuser, machine, terminal, service_name,
logon_time, last_call_et
from v$session
where username = ‘TEST’;

#3 Sessions from a specific machine

select sid, username, program,
decode(state, ‘WAITING’, ‘Waiting’,
‘Working’) state,
last_call_et, seconds_in_wait, event
from v$session
where machine = ‘an23’;

#4 Get the SQL

SQL statement a session is executing, which will provide more insights into the workings of the session

select sql_id
from v$session
where sid = 3089;


Categories: Performance Analysis