Show oracle cpu usage for sessions as percentage
The following scripts return CPU usage for active sessions. The result shows the CPU usage in seconds.
I want the same report using CPU percentage. What's 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#
/
source to share
Long story short: you can't do it with a single query, you will need to write PL / SQL to collect payloads to get useful information.
Oracle has "accumulated time" statistics, which means the engine maintains a continuous usage path. You will need to define the start time and end time of the 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 stats will be affected if you only have CPU # 1 or CPU # 8. Thus, you will need to determine how many CPUs your engine is using.
You can query "cpu_count" from V $ PARAMETER to get this value.
select value into t_cpus
from sys.v_$parameter
where name='cpu_count' ;
Then it's pretty simple:
The maximum total time will be in seconds * number of processors, so if you only have processor # 1 then the maximum total time will be "60", but if you have processors # 2 then the maximum time will be "120". # 3 will be "180" etc ...
So, you use the start and end times of the analyzed period with sysdate:
t_start := sysdate ; t_end := sysdate ;
And now you calculate 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 what it is, "avgcpu" is the value you are looking for.
source to share