My Requirement - Find the CPU utilization by a module for a given period. I am using following 2 queries, which are calculating the CPU utilization for a module say XModule less than 1% for last 24 hours (to_date(sysdate-1) )
SELECT OT.* ,ROUND((TOT_CPU_TIME/SUM(TOT_CPU_TIME) OVER ())*100 ,2)||'%' CPU_PERCENT,SQL_ID_CNT FROM ( SELECT MODULE, COUNT(1) CNT_SQL_ID, SUM(A.CPU_TIME) TOT_CPU_TIME,sum(A.EXECUTIONS) TOTAL_EXECUTIONS,ROUND(SUM(A.CPU_TIME)/sum(A.EXECUTIONS),2) CPU_PER_EXECUTION ,COUNT(SQL_ID) SQL_ID_CNT from v$sql A WHERE 1=1 AND A.LAST_ACTIVE_TIME > TO_DATE(SYSDATE-1) GROUP BY MODULE ) OT ORDER BY ROUND((TOT_CPU_TIME/SUM(TOT_CPU_TIME) OVER ())*100 ,2) DESC;
or
select module_info,SUM(cpu_usage_per_cent) cpu_usage_per_cent from ( select username,sid, round((cpu_usage/( select sum(value) total_cpu_usage from gv$sesstat t inner join gv$session s on ( t.sid = s.sid ) inner join gv$statname n on ( t.statistic# = n.statistic# ) where n.name like '%CPU used by this session%' and nvl(s.sql_exec_start, s.prev_exec_start) >= sysdate-1 ))*100,6) cpu_usage_per_cent, module_info,client_info from ( select nvl(s.username,'Oracle Internal Proc.') username,s.sid,t.value cpu_usage, nvl(s.module, s.program) module_info, decode(s.osuser,'oracle', s.client_info, s.osuser) client_info from gv$sesstat t inner join gv$session s on ( t.sid = s.sid ) inner join gv$statname n on ( t.statistic# = n.statistic# ) where n.name like '%CPU used by this session%' and nvl(s.sql_exec_start, s.prev_exec_start) >= sysdate-1 ) s1 ) where 1=1GROUP BY module_info order by cpu_usage_per_cent desc;
If I use the dba_hist_active_sess_history table then the XMODULE CPU utilization is 90% plus
select ot.* , (cpu_module/total_cpu)*100 cpu_percent from ( SELECT distinct MODULE,session_state, SUM(WAIT_TIME) OVER (PARTITION BY MODULE,session_state) cpu_module ,SUM(WAIT_TIME) OVER (PARTITION BY session_state) total_cpuFROM dba_hist_active_sess_history a, dba_hist_snapshot b WHERE 1=1 AND a.snap_id = b.snap_id AND a.user_id NOT IN (0, 5) AND a.instance_number = b.instance_number AND A.sample_time > TO_DATE(sysdate-1) AND A.sample_time < TO_DATE(sysdate) ) ot where total_cpu > 0 order by (cpu_module/total_cpu)*100 desc ;
Please let me know which one is the correct query and if you have an query which can calculate the CPU utilization module wise for a period it will be great.
I tried the above 3 queries, and I am looking for a query which calculate the CPU utilization for each module for a given period (historical period as well)
