What is below will query AWR and print CPU for a given service in a three instance cluster. This hardcodes the three instances, as well as eight processors with half hour AWR samples (14,400 seconds of CPU available).
set serveroutput on pages 100 feedback off verify off
variable service_name varchar2(100)
exec :service_name := '&service_name'
variable start_time number
begin
select min(snap_id) into :start_time from dba_hist_snapshot where begin_interval_time > sysdate - &start_time;
end;
/
variable end_time number
begin
select min(snap_id) into :end_time from dba_hist_snapshot where begin_interval_time > sysdate - &end_time;
end;
/
print start_time
print end_time
print service_name
select *
from (select distinct begin_interval_time,
trunc(inst1/10000/14400) inst1,
trunc(inst2/10000/14400) inst2,
trunc(inst3/10000/14400) inst3
from (select to_char(begin_interval_time,'YYYY_MM_DD HH24:MI') begin_interval_time,
dhss.instance_number,
value - lag(value, 1, 0) over (order by dhss.instance_number, dhss.snap_id) as value_delta
from dba_hist_snapshot dhs,
dba_hist_service_stat dhss
where dhs.snap_id = dhss.snap_id
and dhs.instance_number = dhss.instance_number
and stat_name = 'DB CPU'
and service_name = :service_name
and dhss.snap_id between :start_time and :end_time
order by 1) c
model
ignore nav
dimension by(c.instance_number i,
c.begin_interval_time begin_interval_time)
measures(c.value_delta value_delta,
0 inst1,
0 inst2,
0 inst3)
rules(inst1[any,any] = value_delta[i = 1, cv(begin_interval_time)],
inst2[any,any] = value_delta[i = 2, cv(begin_interval_time)],
inst3[any,any] = value_delta[i = 3, cv(begin_interval_time)])
order by 1)
where (inst1 >= 0 and inst2>= 0 and inst3>= 0)
/