Home > Performance Analysis > Session State

Session State

#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
  1. No comments yet.
  1. No trackbacks yet.

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 )

Google+ photo

You are commenting using your Google+ 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 )


Connecting to %s

%d bloggers like this: