DBA AWR CPU Load (active session history)

➜ Back to Library
Description
Categories: Enginatics
Repository: Github
Aggregates active sessions by snapshot time to identify times with high CPU load over a specific 'CPU Sessions From' threshold value.
select
xxen_util.client_time(dhash.sample_time_) sample_time,
dhash.instance_number,
dhash.sessions,
xxen_util.zero_to_null(dhash.cpu_sessions) cpu_sessions,
xxen_util.zero_to_null(dhash.waiting_sessions) waiting_sessions
from
(
select distinct
cast(dhash.sample_time as date) sample_time_,
dhash.instance_number,
count(*) over (partition by dhash.sample_id) sessions,
count(decode(dhash.session_state,'ON CPU',1)) over (partition by dhash.sample_id) cpu_sessions,
count(decode(dhash.session_state,'WAITING',1)) over (partition by dhash.sample_id) waiting_sessions
from
dba_hist_active_sess_history dhash
where
1=1
) dhash
where
2=2
order by
sample_time,
instance_number
Parameter NameSQL textValidation
From Time
dhash.sample_time>=:from_time
DateTime
To Time
dhash.sample_time<:to_time+1/86400
DateTime
CPU Sessions From
dhash.cpu_sessions>=:cpu_sessions_from
Number
Session Type
dhash.session_type=upper(:session_type)
LOV
Instance Id
dhash.instance_number=:inst_id
LOV
Diagnostic Pack enabled
0=0
LOV
Container Data
dhash.dbid=(select vd.dbid from v$database vd)
LOV
Download
Related Blitz Reports
DBA AWR Wait Event Summary (active session history) DBA AWR Settings DBA AWR Blocking Session Summary DBA AWR Active Session History DBA AWR System Metrics Summary DBA SGA Active Session History DBA AWR CPU vs Wait Time Summary DBA SGA+PGA Memory Configuration DBA AWR Wait Event Summary
Blitz Report™