CPU Usage by User Session

Oracle system views can be used to find the amount of CPU consumed by each database session. This script will display the CPU information:

SELECT
s.username, t.SID, p.spid DB_OS_ID,
t.VALUE/100 cpu_usage_seconds
FROM v$session s, v$sesstat t, v$statname n, v$process p
WHERE t.STATISTIC# = n.STATISTIC#
and n.NAME like '%CPU used by this session%'
and t.SID = s.SID
and s.paddr(+) = p.addr
and s.status='ACTIVE'
and s.username is not null
order by VALUE desc;

The OS commend "ps -ef" also gives CPU info in the 4th column of the output.

To troubleshoot performance issue, you can use below query and the session ID from above query to find the SQL statement for tuning candidate:

SELECT s.username, s.osuser, s.machine, s.program, s.status, s.sid, s.serial#, s.process client_pid, p.spid DB_os_pid, t.sql_text, t.disk_reads, t.executions
FROM v$session s, v$process p, v$sqlarea t
WHERE s.paddr = p.addr and s.sql_address = t.address(+)
and s.sid = &SID

In Oracle 11g, if the init parameter audit_trail is set to DB level by

SQL> alter system set audit_trail='DB' scope=spfile;

And then bounce the database, view dba_audit_session (or sys.aud$) has a column SESSION_CPU displaying session's CPU usage.