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:-)