DBA SGA CPU Load (active session history)

select
xxen_util.client_time(gash.sample_time_) sample_time,
gash.inst_id,
gash.sessions,
xxen_util.zero_to_null(gash.cpu_sessions) cpu_sessions,
xxen_util.zero_to_null(gash.waiting_sessions) waiting_sessions
from
(
select distinct
cast(gash.sample_time as date) sample_time_,
gash.inst_id,
count(*) over (partition by gash.sample_id) sessions,
count(decode(gash.session_state,'ON CPU',1)) over (partition by gash.sample_id) cpu_sessions,
count(decode(gash.session_state,'WAITING',1)) over (partition by gash.sample_id) waiting_sessions
from
gv$active_session_history gash
where
1=1
) gash
where
2=2
order by
sample_time desc,
inst_id

select xxen_util.client_time(gash.sample_time_) sample_time, gash.inst_id, gash.sessions, xxen_util.zero_to_null(gash.cpu_sessions) cpu_sessions, xxen_util.zero_to_null(gash.waiting_sessions) waiting_sessions from ( select distinct cast(gash.sample_time as date) sample_time_, gash.inst_id, count(*) over (partition by gash.sample_id) sessions, count(decode(gash.session_state,'ON CPU',1)) over (partition by gash.sample_id) cpu_sessions, count(decode(gash.session_state,'WAITING',1)) over (partition by gash.sample_id) waiting_sessions from gv$active_session_history gash where 1=1 ) gash where 2=2 order by sample_time desc, inst_id