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 (%)';

Oracle TOP SQL

select sql_text,
username,
disk_reads_per_exec,
buffer_gets_per_exec,
buffer_gets,
disk_reads,
parse_calls,
sorts,
executions,
loads,
rows_processed,
hit_ratio,
first_load_time,
sharable_mem,
persistent_mem,
runtime_mem,
cpu_time_secs,
cpu_time_secs_per_execute,
elapsed_time_secs,
elapsed_time_secs_per_execute,
address,
hash_value
from
(select sql_text,
b.username,
round ((a.disk_reads/decode(a.executions,0,1,a.executions)),2) disk_reads_per_exec,
a.disk_reads,
a.buffer_gets,
round ((a.buffer_gets/decode(a.executions,0,1,a.executions)),2) buffer_gets_per_exec,
a.parse_calls,
a.sorts,
a.executions,
a.loads,
a.rows_processed,
100 - round(100* a.disk_reads/greatest(a.buffer_gets,1),2) hit_ratio,
a.first_load_time,
sharable_mem,
persistent_mem,
runtime_mem,
round(cpu_time / 1000000,3) cpu_time_secs,
round((cpu_time / 1000000) / decode (a.executions,0,1,a.executions),3) cpu_time_secs_per_execute,
round(elapsed_time / 1000000,3) elapsed_time_secs,
round((elapsed_time / 1000000)/decode(a.executions,0,1,a.executions),3) elapsed_time_secs_per_execute,
address,
hash_value
from sys.v_$sqlarea a, sys.all_users b
where a.parsing_user_id=b.user_id and
b.username not in ('SYS','SYSTEM')
order by 3 desc)
where rownum < 15;


SELECT /* SQL Monitor Query for >= 10g */
'(' || s.SID || ',' || s.serial# || ')' "sid/serial",
s.program "pgrm",
s.terminal "term",
s.username "db user",
DECODE (ROUND (s.last_call_et / 60), '0', '< 1', ROUND (s.last_call_et / 60)) "rtime (mins)",
DECODE (ps."px oper cnt", '', 'N/A', ps."px oper cnt") "px opers",
DECODE (ps."px count", '', 'N/A', ps."px count") "px slaves",
si.block_changes "sess bchgs",
si.physical_reads "sess preads",
pss_pr."px preads" "child px preads",
si.consistent_gets "sess cgets",
pss_cg."px cgets" "child px cgets",
ss_cpu."sess cpu" "sess cpu",
pss_cpu."px cpu" "child px cpu",
sa.optimizer_cost "curr sql cost",
su."blocks" "temp blocks",
t."used_ublk" "undo blocks",
s.event "wait",
s.seconds_in_wait "wait secs",
s.state "wait state",
s.sql_id "current sql id",
sa.sql_fulltext "sql text",
DBMS_XPLAN.display_cursor (s.sql_id, s.sql_child_number) "sql xplan"
FROM v$session s,
v$sess_io si,
v$sql sa,
(SELECT qcsid, COUNT (DISTINCT server_set) "px oper cnt",
COUNT (*) "px count"
FROM v$px_session
WHERE NOT server_set IS NULL
GROUP BY qcsid, DEGREE) ps,
(SELECT qcsid, SUM (VALUE) "px preads"
FROM v$px_sesstat
WHERE statistic# = 54 AND SID != qcsid
GROUP BY qcsid) pss_pr,
(SELECT qcsid, SUM (VALUE) "px cgets"
FROM v$px_sesstat pss
WHERE statistic# = 50 AND SID != qcsid
GROUP BY qcsid) pss_cg,
(SELECT qcsid, SUM (VALUE) "px cpu"
FROM v$px_sesstat pss
WHERE statistic# = 12 AND SID != qcsid
GROUP BY qcsid) pss_cpu,
(SELECT ss.SID, SUM (ss.VALUE) "sess cpu"
FROM v$sesstat ss
WHERE statistic# = 12
GROUP BY ss.SID) ss_cpu,
(SELECT t.ses_addr, SUM (t.used_ublk) "used_ublk"
FROM v$transaction t
GROUP BY t.ses_addr) t,
(SELECT su.session_addr, SUM (su.blocks) "blocks"
FROM v$sort_usage su
GROUP BY su.session_addr) su
WHERE s.sql_address = sa.address
AND s.sql_hash_value = sa.hash_value
AND s.saddr = su.session_addr(+)
AND s.SID = ps.qcsid(+)
AND s.SID = si.SID(+)
AND s.saddr = t.ses_addr(+)
AND s.SID = pss_pr.qcsid(+)
AND s.SID = pss_cg.qcsid(+)
AND s.SID = pss_cpu.qcsid(+)
AND s.SID = ss_cpu.SID(+)
AND s.TYPE != 'BACKGROUND'
AND s.status = 'ACTIVE'
AND program NOT LIKE ('%(C%') --Eliminate Streams Capture
AND program NOT LIKE ('%(A%') --Eliminate Streams Apply
AND program NOT LIKE ('%(P%') --Eliminate Parallel Slaves
ORDER BY sa.optimizer_cost DESC;