Tuesday, October 27, 2015

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                           699        1450
SYS                                             813          539
ORACLE USER                           583          425
ORACLE USER                             2            387
ORACLE USER                           587          133
ORACLE USER                           235             2

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)

Thanks,
Chowdari

1 comment:

Some Most Popular Articles