Wednesday, August 17, 2011

Checking Session Statistics Part -II

CPU Usage By Session:

Username - Name of the user
SID - Session id
CPU Usage - CPU centiseconds used by this session (divide by 100 to get real CPU seconds)

select nvl(ss.USERNAME,'ORACLE PROC') username,
VALUE cpu_usage
from v$session ss, 
v$sesstat se, 
v$statname sn
where   se.STATISTIC# = sn.STATISTIC#
and   NAME like '%CPU used by this session%'
and   se.SID = ss.SID
order   by VALUE desc

Output: -

Resources usage by users: -

SID - Session ID
Username - Name of the user
Statistic - Name of the statistic
Value - Current value

select ses.SID,
nvl(ses.USERNAME,'ORACLE PROC') username,
sn.NAME statistic,
from v$session ses, 
v$statname sn, 
v$sesstat sest
where ses.SID = sest.SID
and sest.VALUE is not null
and sest.VALUE != 0            
order by ses.USERNAME, ses.SID, sn.NAME

Output: -


No comments: