How to find top CPU-consuming sessions in Oracle

Rank active user sessions by CPU used since login, from V$SESSTAT. Joins to V$PROCESS for OS PID so you can correlate with OS tools (top, pidstat) when a database is CPU-bound right now.

SET LINES 500 PAGES 500
COL program       FORMAT a30 HEADING "Program"
COL cpu_usage_sec FORMAT 999999999 HEADING "CPU in Seconds"
COL module        FORMAT a18
COL osuser        FORMAT a40
COL username      FORMAT a15
COL ospid         FORMAT a06 HEADING "OS PID"
COL sid           FORMAT 99999
COL serial#       FORMAT 999999
COL sql_id        FORMAT a15
COL machine       FORMAT a25

SELECT *
FROM (
  SELECT p.spid AS ospid,
         ss.sid, ss.serial#, ss.sql_id, ss.username,
         SUBSTR(ss.program, 1, 30) AS program,
         ss.module, ss.osuser, ss.machine, ss.status,
         se.value / 100 AS cpu_usage_sec
  FROM   v$session  ss,
         v$sesstat  se,
         v$statname sn,
         v$process  p
  WHERE  se.statistic# = sn.statistic#
  AND    sn.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    se.value      > 0
  ORDER BY se.value DESC
)
WHERE rownum < 11;