CPU Usage By Session

CPU Usage By Session

select nvl(ss.USERNAME,'ORACLE PROC') username, 
se.SID,ss.status, 
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 and ss.username='%PROD%'
order by VALUE desc 
/ 

----------------------------------------------------

Description

Script displays details so the session which have used the most CPU. Needs the Init.Ora parameter timed_statistics set to true to work.


col program form a30 heading "Program"
col CPUMins form 99990 heading "CPU Mins"
select rownum as rank, a.*
from (
    SELECT v.sid, program, v.value / (100 * 60) CPUMins
    FROM v$statname s , v$sesstat v, v$session sess
   WHERE s.name = 'CPU used by this session'
     and sess.sid = v.sid
     and v.statistic#=s.statistic#
     and v.value>0
   ORDER BY v.value DESC) a
where rownum < 11;

------------------------------------------------------


Resource Usage by a User:

------------------------------------------------------

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 
/ 
------------------------------------------------------