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;