Wednesday, June 1, 2022
top 10 cpu sql
select * from (
select p.spid "ospid",ss.INST_ID,
(se.SID),ss.serial#,ss.SQL_ID,ss.username,substr(ss.program,1,30) "program",
ss.module,ss.osuser,ss.MACHINE,ss.status,
se.VALUE/100 cpu_usage_sec
from gv$session ss,gv$sesstat se,
gv$statname sn,gv$process p
where
se.STATISTIC# = sn.STATISTIC#
and NAME like '%CPU used by this session%'
and se.SID = ss.SID
and ss.username !='SYS'
and ss.status='ACTIVE'
and ss.username is not null
and ss.paddr=p.addr and value > 0
and ss.INST_ID=3
order by se.VALUE desc);
select rownum, a.*
from (
SELECT v.sid,sess.Serial# ,v.INST_ID,sess.SQL_ID,program, v.value / (100 * 60) CPUMins
FROM gv$statname s , gv$sesstat v, gv$session sess
WHERE s.name = 'CPU used by this session'
and sess.sid = v.sid
and v.statistic#=s.statistic#
and v.value>0
and v.INST_ID=3
ORDER BY v.value DESC) a
where rownum < 11;
Memeory Usage
SELECT a.username, a.osuser,a.INST_ID, a.program, b.spid,b.pga_used_mem/1024/1024/1024,
a.sid, a.serial# ,a.module,a.logon_time,a.terminal FROM gv$session a, gv$process b WHERE a.paddr = b.addr
and a.USERNAME='XXXXXXXXX'
order by
b.pga_used_mem desc
Subscribe to:
Posts (Atom)