Master Yavor from Tryavna

A few days ago I posted a query that returns the average load for given database per day, week or month. Works great on single instance databases, but not in RAC environments

RAC databases have one line per instance in DBA_HIST views, so my strategy of subtracting the value from the previous line returns very strange results. So here is the RAC-aware version of the query:

select trunc(this_day, 'MONTH'), avg(day_db_time), sum(cnt) scnt
  from (select this_day, count(1) cnt,
                sum(case
                       when snap_db_time > 0 then
                        snap_db_time/1000/1000/60/60/24
                       else
                        null
                     end) day_db_time
           from (select trunc(begin_interval_time) this_day, tm.snap_id,
                         value - lag(value, 1) over(order by tm.instance_number, tm.snap_id) snap_db_time
                    from dba_hist_sys_time_model tm, dba_hist_snapshot s
                   where tm.stat_name = 'DB CPU'
                     and s.snap_id = tm.snap_id
                     and s.instance_number = tm.instance_number
                     and s.begin_interval_time > add_months(trunc(sysdate, 'YEAR'), -24)
                     and s.begin_interval_time < trunc(sysdate, 'MONTH')
                     )
          group by this_day)
 group by trunc(this_day, 'MONTH')
 order by 1

The only difference is, I'm careful which is the instance on the previous line. This statement works also on single-instance databases.