Cpu utilization per module in database
Hello, I want to calculate the cpu consumption per module in my database. The basic aim is whether we can add more users or not.
I am using the following query:
set linesize 200
set pagesize 120
col module for a60
SELECT mymodule "Module", SUM (cpu_time) "CPU Time", SUM (wait_time) "Wait
Time",
SUM (cpu_time) + SUM (wait_time) "Total Time"
FROM (SELECT a.module mymodule,
(CASE (session_state)
WHEN 'ON CPU'
THEN wait_time / 100
END
) cpu_time,
(CASE (session_state)
WHEN 'WAITING'
THEN time_waited / 100
END
) wait_time
FROM dba_hist_active_sess_history a, dba_hist_snapshot b
WHERE --b.end_interval_time > sysdate-1