sql writers block

  • From: Scott <oraracdba@xxxxxxxxx>
  • To: oracle-l@xxxxxxxxxxxxx
  • Date: Mon, 9 Feb 2009 13:32:15 -0800 (PST)

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: