Re: sql writers block
- From: Riyaj Shamsudeen <riyaj.shamsudeen@xxxxxxxxx>
- To: oraracdba@xxxxxxxxx
- Date: Mon, 9 Feb 2009 18:52:21 -0600
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@xxxxxxxxx> 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
>
>
>
>
> --
> //www.freelists.org/webpage/oracle-l
>
>
>
Other related posts:
