Show oracle CPU usage for sessions as percentage -

the following scripts returns cpu usage active sessions. result shows cpu usage in seconds.

what need same report cpu usage in percentage. best way this?

-- -- show cpu usage active sessions --  set pause on set pause 'press return continue' set pagesize 60 set linesize 300  column username format a30 column sid format 999,999,999 column serial# format 999,999,999 column "cpu usage (seconds)"  format 999,999,999.0000  select    s.username,    t.sid,    s.serial#,    sum(value/100) "cpu usage (seconds)"    v$session s,    v$sesstat t,    v$statname n    t.statistic# = n.statistic# ,    name '%cpu used session%' ,    t.sid = s.sid ,    s.status='active' ,    s.username not null group username,t.sid,s.serial# / 

long story short: won't able single query, need write pl/sql gather useful data in order obtain useful information.

oracle has "accumulated time" statistics, means engine keeps continous track of use. have define start time , end time analysis.

you can query 'db cpu' v$sys_time_model

select value t_db_cpu_i sys.v_$sys_time_model stat_name = 'db cpu' ;  /* start time */  ... select value t_db_cpu_f sys.v_$sys_time_model stat_name = 'db cpu' ; /* end time */ 

cpu statistics affected if have #1 cpu or #8 cpus. so, have determine how many cpus engine using.

you can query 'cpu_count' v$parameter obtain value.

select value t_cpus sys.v_$parameter name='cpu_count' ; 

then, it's quite simple:

maximum total time seconds * number of cpus, if have #1 cpu maximum total time "60" , if have #2 cpus maximun total time "120" .. #3 cpus "180" .. etc. ...

so take start time , end time of analyzed period using sysdate:

t_start := sysdate ; t_end := sysdate ; 

and compute following:

seconds_elapsed := (t_end - t_start)*24*60*60 ; total_time := seconds_elapsed * t_cpus ;  used_cpu := t_db_cpu_f - t_db_cpu_i ; secs_cpu := seconds_elapsed/1000000 ; avgcpu := (secs_cpu/total_time)*100 ; 

and that's it, "avgcpu" value looking for.