Show oracle CPU usage for sessions as percentage

Question

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

What I need is the same report with cpu usage in percentage. What is the best way to do this?

--
-- Show CPU Usage for Active Sessions
--

SET PAUSE ON
SET PAUSE 'Press Return to 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) as "cpu usage (seconds)"
FROM
   v$session s,
   v$sesstat t,
   v$statname n
WHERE
   t.STATISTIC# = n.STATISTIC#
AND
   NAME like '%CPU used by this session%'
AND
   t.SID = s.SID
AND
   s.status='ACTIVE'
AND
   s.username is not null
GROUP BY username,t.sid,s.serial#
/

Answer 1

Long story short: you won't be able to do it with a single query, you will need to write a PL/SQL to gather useful data in order to obtain useful information.

Oracle has "accumulated time" statistics, this means that the engine keeps a continous track of use. You will have to define a start time and an end time for analysis.

You can query 'DB CPU' from V$SYS_TIME_MODEL

select value into t_db_cpu_i
from sys.V_$SYS_TIME_MODEL
where stat_name = 'DB CPU' ;  /* start time */ 
...
select value into t_db_cpu_f
from sys.V_$SYS_TIME_MODEL
where stat_name = 'DB CPU' ; /* end time */

CPU statistics will be affected if you have just #1 CPU or #8 CPUs. So, you will have to determine how many CPUs is your engine using.

You can query 'cpu_count' from V$PARAMETER to obtain this value.

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

Then, it's quite simple:

Maximum total time will be seconds * number of CPUs, so if you have just #1 CPU then maximum total time would be "60" , but if you have #2 CPUs then maximun total time would be "120" .. #3 CPUs will be "180" .. etc. ...

So you take start time and end time of the analyzed period using sysdate:

t_start := sysdate ;
t_end := sysdate ;

And now you compute the 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" is the value you are looking for.