Re: sql writers block

This can be done using analytical sql. I have done similar things with statspack data. If I understand the query, you are gathering the values listed for the previous hour from the runtime of the query grouped by snap_id.

The values are monotonically increasing, so you can calculate the value for any snap_id interval by calculating the deltas using the LAG function. I don't have access to a good AWR licensed db right now, so I can't get a script for you. However, the basics of analytical sql and statspack (similar in structure to some AWR views) is at http://www.optimaldba.com/papers/PMUSaAS.pdf

Regards,
Daniel Fink

-- 
Daniel Fink

OptimalDBA.com - Oracle Performance, Diagnosis, Data Recovery and Training

OptimalDBA    http://www.optimaldba.com
Oracle Blog   http://optimaldba.blogspot.com

Lost Data?    http://www.ora600.be/



Scott 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