Re: sql writers block
Date: Mon, 9 Feb 2009 18:52:21 -0600
Message-ID: <203315c10902091652g38080ca9ia0f1d0b8097fb366_at_mail.gmail.com>
Hi Scott
I think, following SQL might be a good start.. I haven't tested this thoroughly and so, please test it..
with stats as (
select begin_interval_time, instance_number, snap_id,
case when stat_name='DB CPU' then
value - lag(value,1,0) over( partition by stat_id, dbid, instance_number ,startup_time order by snap_id )
end dbcpu,
case when stat_name='DB time' then
value - lag(value,1,0) over( partition by stat_id, dbid, instance_number ,startup_time order by snap_id )
end dbtime,
case when stat_name='background elapsed time' then
value - lag(value,1,0) over( partition by stat_id, dbid, instance_number ,startup_time order by snap_id )
end dbbgela,
case when stat_name='background cpu time' then
value - lag(value,1,0) over( partition by stat_id, dbid, instance_number ,startup_time order by snap_id )
end dbbgcpu
from
(
select h.begin_interval_time, s.snap_id, s.dbid, s.instance_number,
s.stat_id, s.stat_name, value, startup_time
FROM DBA_HIST_SYS_TIME_MODEL s , DBA_HIST_SNAPSHOT h
where
s.instance_number = h.instance_number and s.snap_id = h.snap_id and s.dbid = h.dbid and s.stat_name in ('DB time','DB CPU','background elapsed time','backgroundcpu time') and
h.begin_interval_time > sysdate -1
order by stat_id, instance_number,snap_id )
)
select begin_interval_time, instance_number, max(dbcpu), max(dbtime),max(dbbgela),max(dbbgcpu)
from stats
group by begin_interval_time, instance_number order by instance_number, begin_interval_time /
-- Cheers Riyaj Shamsudeen Principal DBA, Ora!nternals - http://www.orainternals.com Specialists in Performance, Recovery and EBS11i Blog: http://orainternals.wordpress.com On Mon, Feb 9, 2009 at 3:32 PM, Scott <oraracdba_at_yahoo.com> wrote:Received on Mon Feb 09 2009 - 18:52:21 CST
>
> 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
>
>
>
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>
-- http://www.freelists.org/webpage/oracle-l