CPU usage

CPU usage for user:


select ss.username,
se.SID,
VALUE / 100 cpu_usage_seconds,
ss.LOGON_TIME,
(sysdate - ss.logon_time) * 24 * 3600 as delka_logon_sec,
sysdate as aktual_time
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.status = 'ACTIVE'
--and ss.username is not null
and nvl(value, 0) > 7000
and se.sid not in
(select sid from V$SESSION where AUDSID = userenv('SESSIONID'))
order by VALUE desc;

CPU usage for users + selects:


select ss.username, se.SID, VALUE / 100 cpu_usage_seconds, sel.*
from v$session ss,
v$sesstat se,
v$statname sn,
(select a.USERNAME,
c.sql_text,
a.SID,
(select j.what
from dba_jobs_running jr, dba_jobs j
where jr.SID = a.sid
and jr.job = j.job) as job,
a.process,
a.program,
a.module,
a.machine
from v$sql c, v$session a
where a.sql_address = c.address
and a.sql_hash_value = c.hash_value) sel
where se.STATISTIC# = sn.STATISTIC#
and NAME like '%CPU used by this session%'
and se.SID = ss.SID
and ss.status = 'ACTIVE'
and ss.username is not null
and se.sid = sel.sid(+)
order by VALUE desc;

Session CPU statistics:


select ss.username, se.SID, VALUE / 100 cpu_usage_seconds, name
from v$session ss, v$sesstat se, v$statname sn
where se.STATISTIC# = sn.STATISTIC#
and ((NAME like '%CPU used by this session%') or
(name like '%ecursive cpu usage%'))
and se.SID = ss.SID
and ss.status = 'ACTIVE'
and ss.username is not null
and value <> 0
order by name, VALUE desc;

 

Leave a Reply

Your email address will not be published. Required fields are marked *