
rem Session CPU consumption statistic compare
rem Craig Shallahamer, craig@orapub.com

set tab off
col sid           format 99990
col ss_sec        format 99990.00
col program       format a22 trunc
col db_cpu_sec    format 99990.00
col bg_cpu_sec    format 99990.00

select ss.sid, se.program, ss.value/100 ss_sec,
       stmfg.value/1000000 db_cpu_sec, stmbg.value/1000000 bg_cpu_sec
from   v$session se,
       v$sesstat ss,
       v$statname sn,
       v$sess_time_model stmbg,
       v$sess_time_model stmfg
where  se.sid = ss.sid 
  and  se.sid = stmfg.sid
  and  se.sid = stmbg.sid 
  and  stmfg.stat_name = 'DB CPU'
  and  stmbg.stat_name = 'background cpu time'
  and  ss.statistic# = sn.statistic#
  and  sn.name = 'CPU used by this session'
order by 3 desc, 5 desc, 1
/


