Query to find Oracle Process consuming most of the Memory and CPU


Session consuming the most memory

SELECT a.username, a.osuser, a.program, b.spid,b.pga_used_mem, a.sid, a.serial# ,a.module,a.logon_time,a.terminal FROM v$session a, v$process b WHERE a.paddr = b.addr
order by
b.pga_used_mem desc;

OR 

SET LINES 200
COLUMN username FORMAT A15
COLUMN program FORMAT A25
COLUMN module FORMAT A25
COLUMN kill_command FORMAT A60

SELECT
    s.sid,
    s.serial#,
    s.username,
    s.program,
    s.module,
    ROUND(pga_used_mem / 1024 / 1024, 2) AS pga_used_mb,
    ROUND(pga_alloc_mem / 1024 / 1024, 2) AS pga_alloc_mb,
    ROUND(pga_max_mem / 1024 / 1024, 2) AS pga_max_mb,
    'ALTER SYSTEM KILL SESSION ''' || s.sid || ',' || s.serial# || ''' IMMEDIATE;' AS kill_command
FROM
    v$session s
JOIN
    v$process p ON s.paddr = p.addr
WHERE
    s.username IS NOT NULL
    AND s.type != 'BACKGROUND'
ORDER BY
    pga_used_mem DESC;



Session Consuming Most CPU

select 
   ss.username,
   se.SID,
   VALUE/100 cpu_usage_seconds
from
   v$session ss, 
   v$sesstat se, 
   v$statname sn
where
   se.STATISTIC# = sn.STATISTIC#
and
   NAME like '%CPU used by this session%'
and
   se.SID = ss.SID
and 
   ss.status='ACTIVE'
and 
   ss.username is not null
order by 3 desc