Oracle CPU USAGE

SELECT n.username,
s.sid,
s.value
FROM v$sesstat s,v$statname t, v$session n
WHERE s.statistic# = t.statistic#
AND n.sid = s.sid
AND t.name='CPU used by this session'
ORDER BY s.value desc;


select * from v$sesstat
where statistic# = 12
order by value desc;


Total CPU time = parsing CPU usage + recursive CPU usage + other CPU usage:

SELECT name, value
FROM V$SYSSTAT
WHERE NAME IN ('CPU used by this session',
'recursive cpu usage',
'parse time cpu');


To determine percentage of CPU usage for parsing:

select (a.value / b.value)*100 "% CPU for parsing"
from V$SYSSTAT a, V$SYSSTAT b
where a.name = 'parse time cpu'
and b.name = 'CPU used by this session';


To determine percentage of Recursive CPU Usage:

select (a.value / b.value)*100 "% recursive cpu usage"
from V$SYSSTAT a, V$SYSSTAT b
where a.name = 'recursive cpu usage'
and b.name = 'CPU used by this session';


SELECT sql_id,
DECODE(wait_time, 0, 'WAITING', 'ON CPU'),
COUNT(*),
SUM(time_waited)
FROM v$active_session_history
WHERE sample_time > sysdate - 5/60/24
AND sample_time < sysdate
GROUP BY
sql_id, DECODE(wait_time, 0, 'WAITING', 'ON CPU')


select SAMPLE_TIME, TIME_WAITED, EVENT
from v$active_session_history
where TIME_WAITED > 0
and DECODE(wait_time, 0, 'WAITING', 'ON CPU') = 'WAITING'
and SQL_ID = '38n25251qmh23'


SELECT * FROM
(SELECT s.sid,
p.spid,
s.status,
s.username,
s.osuser,
a.sql_text ,
a.cpu_time
fROM v$sqlarea a, v$session s, v$process p
where a.hash_value = s.sql_hash_value
and s.paddr = p.addr
ORDER BY a.cpu_time DESC)
WHERE rownum <= 10 ;



select * from v$sysmetric where metric_name= 'Host CPU Utilization (%)';

select s.BEGIN_TIME,
s.END_TIME,
s.METRIC_NAME,
s.MAXVAL,
s.MINVAL,
s.AVERAGE
from v$sysmetric_summary s
where metric_name= 'Host CPU Utilization (%)';


select * from v$sysmetric_history where metric_name= 'Host CPU Utilization (%)';