Either this can't be done in SQL, If it can then I am missing something. The
goal is break this information based on snap_id between a given time. This
probably can be done with lots of inline sql but I want to keep this as simple
as possible.
Thanks in advance
Scott
select b.snap_id
,sum(case when e.stat_name = 'DB time'
then e.value - b.value
else 0
end) tdbtim
, sum(case when e.stat_name = 'DB CPU'
then e.value - b.value
else 0
end) tdbcpu
, sum(case when e.stat_name = 'background elapsed time'
then e.value - b.value
else 0
end) tbgtim
, sum(case when e.stat_name = 'background cpu time'
then e.value - b.value
else 0
end) tbgcpu
from dba_hist_sys_time_model b
, dba_hist_sys_time_model e
where e.instance_number = b.instance_number
and b.snap_id = (select max(snap_id)
from dba_hist_snapshot
where
END_INTERVAL_TIME between to_date(:BDATE,'yyyymmddhh24mi')-(1/24)
and
to_date(:BDATE,'yyyymmddhh24mi'))
and e.snap_id = (select max(snap_id)
from dba_hist_snapshot
where
END_INTERVAL_TIME between to_date(:EDATE,'yyyymmddhh24mi')-(1/24)
and
to_date(:EDATE,'yyyymmddhh24mi'))
and b.stat_id = e.stat_id
and e.stat_name in ('DB time','DB CPU'
,'background elapsed time','background cpu time')
group by b.snap_id
--
//www.freelists.org/webpage/oracle-l
