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
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
Nice Query.
ReplyDelete