Thursday 12 December 2013

Oracle database: Check top 10 session CPU consumption

select 'top cpu process', sid, username,
       total_user_cpu,
       round(100 * total_user_cpu/greatest(total_cpu,1),2), sql_id
from (select b.sid sid, nvl(b.username, p.name) username,
             sum(value) total_user_cpu, b.sql_id
      from v$statname c, v$sesstat a,
           v$session b, v$bgprocess p
      where a.statistic# = c.statistic#
      and p.paddr (+) = b.paddr
      and b.sid = a.sid
      and c.name = 'CPU used by this session'
      and b.username not in ('SYS', 'SYSTEM', 'SYSMAN', 'DBSNMP')
      group by b.sid, nvl(b.username, p.name), b.sql_id
      order by 3 desc),
     (select sum(value) total_cpu
      from v$statname c, v$sesstat a,
           v$session b, v$bgprocess p
      where a.statistic# = c.statistic#
      and p.paddr (+) = b.paddr
      and b.sid = a.sid
      and c.name = 'CPU used by this session')
where rownum <= 10;

Take the sid and put in below

select vs.sid,vs.serial#,vs.username,vs.osuser,vs.machine,vs.program,vp.spid from v$session vs, v$process vp where vs.sid = &sid;

No comments:

Post a Comment

Number of Visitors