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,
se.SID,
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,
sest.VALUE
from v$session ses, 
v$statname sn, 
v$sesstat sest
where ses.SID = sest.SID
and sn.STATISTIC# = sest.STATISTIC#
and sest.VALUE is not null
and sest.VALUE != 0            
order by ses.USERNAME, ses.SID, sn.NAME

Output: -











Enjoy:-)

No comments: