12.1.0.2
There are a few ways to get CPU used by a session. something like this works fine
select * from (
select p.spid "ospid",
(se.SID),ss.serial#,ss.SQL_ID,ss.username,substr(ss.program,1,22) "program",ss.module,ss.osuser,ss.MACHINE,ss.status,
se.VALUE/100 cpu_usage_sec
from
v$session ss,
v$sesstat se,
v$statname sn,
v$process p
where
se.STATISTIC# = sn.STATISTIC#
and
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 value > 0
order by se.VALUE desc);
But this shows me by session. For my question here, Im not interested in by session.
If I go to enterprise manager I can see top activity and can see the activity pane which shows me current % breakdown by all waits for a SQL_ID. Its that info for CPU only I want in a script. Possible?