topcpu.sql

---top CPU Session
set linesize 200
column inst_id format 999
column sid format 9999
column "OS PID" format a12
column module format a45
column APP_USER_NAME format a15
column username format a12
SELECT s.inst_id,
       s.sid,
       s.serial#,
       p.spid as "OS PID",
       s.username,s.client_identifier APP_USER_NAME,
       s.module,
       st.value / 100 as "CPU sec"
  FROM gv$sesstat st, gv$statname sn, gv$session s, gv$process p
 WHERE st.inst_id = sn.inst_id and sn.inst_id = s.inst_id and s.inst_id = p.inst_id
 and sn.name = 'CPU used by this session' -- CPU
 AND st.statistic# = sn.statistic# AND st.sid = s.sid AND s.paddr = p.addr AND s.last_call_et < 1800 -- active within last 1/2 hour
 AND s.logon_time > (SYSDATE - 240/1440) -- sessions logged on within 4 hours
 AND st.value / 100 > 10
 ORDER BY st.value ;
exit;