Find out CPU usage for each session

Find out CPU usage for each session

Below query will be useful to find out CPU usage for each session.

select nvl(ss.USERNAME,'ORACLE USER') 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;

Sample Output:

USERNAME                              SID  CPU_USAGE
------------------------------ ---------- ----------
ORACLE USER                           851    2669184
SAPSR3                                      105     913083
SAPSR3                                      1103     897442
SAPSR3                                      359     840508
SAPSR3                                       956     805579
ORACLE USER                           901     738631

Here;

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