Re: sql writers block

From: Riyaj Shamsudeen <riyaj.shamsudeen_at_gmail.com>
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','background
cpu 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:


>
> 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
Received on Mon Feb 09 2009 - 18:52:21 CST

Original text of this message