Oracle: CPU Used by Each Database Session

Oracle exposes cumulative database CPU time for each session through V$SESSTAT. Join it to V$STATNAME by name—never hard-code a statistic number, because statistic numbers can change between releases.

Current CPU by session

SELECT s.sid,
       s.serial#,
       s.username,
       s.status,
       ROUND(ss.value / 100, 2) AS cpu_seconds
FROM   v$session  s
JOIN   v$sesstat  ss ON ss.sid = s.sid
JOIN   v$statname sn ON sn.statistic# = ss.statistic#
WHERE  sn.name = 'CPU used by this session'
AND    s.username IS NOT NULL
ORDER  BY ss.value DESC;

The statistic is cumulative for the life of the session and is traditionally recorded in hundredths of a second. It is Oracle database CPU time, not a live operating-system CPU percentage.

Measure a useful interval

To identify sessions consuming CPU now, capture the query twice over a known interval and compare the change in VALUE. A lifetime total can rank long-running sessions highly even when they are currently idle.

Follow the session to its SQL

SELECT s.sid,
       s.serial#,
       s.sql_id,
       s.event,
       s.state,
       s.module,
       s.machine
FROM   v$session s
WHERE  s.sid = :sid;

• Use SQL_ID with V$SQL and DBMS_XPLAN.DISPLAY_CURSOR.
• Correlate database CPU with host tools; elapsed time, CPU time, and wait time are different measurements.
• Take care with RAC and multitenant scope by using the matching GV$ views and container identifiers when required.

Reference

Oracle documents V$SESSTAT and explicitly recommends resolving statistics through V$STATNAME.