Generating chargeback costs using AWR

One of the best things about AWR is the fact that it tracks CPU at a database service level. This allows us to calculate the amount of CPU used by a service in any number of ways. Once you can gain agreement with the business on the most accurate way to determine the CPU utilization (peak, median, average, etc.), you can generate a chargeback model. For example, we have a three node 24 processor RAC. If we find that the batch service uses 25% of the CPU time in the cluster, then we can say that it is using six CPU’s. We can then take our price per processor amount we have negotiated with Oracle and multiply that by six. The batch service users are then responsible for whatever that total is.

You can use what is below to calculate it. It should be noted that this is for a three node cluster.

create or replace type service_cpu_t as object (service_name varchar2(100),
                                                begin_interval_time varchar2(30), 
                                                cpu1 number, cpu2 number, cpu3 number)
/

create or replace type service_cpu_o as table of service_cpu_t
/

create or replace function service_cpu return service_cpu_o pipelined
  is
  cursor cur_service(p_service_name varchar2) is
    select *
      from (select distinct begin_interval_time,
                            trunc(inst1/10000/14400) inst1,
                            trunc(inst2/10000/14400) inst2,
                            trunc(inst3/10000/14400) inst3
              from (select to_char(begin_interval_time,'YYYY_MM_DD HH24:MI') begin_interval_time,
                           dhss.instance_number,service_name,
                           value - lag(value, 1, 0) over (order by dhss.instance_number, dhss.snap_id) as value_delta
                      from dba_hist_snapshot dhs,
                           dba_hist_service_stat dhss
                      where dhs.snap_id = dhss.snap_id
                        and dhs.instance_number = dhss.instance_number
                        and service_name = p_service_name
                        and stat_name = 'DB CPU'
                      order by 1) c
              model
                ignore nav
                dimension by(c.instance_number i,
                             c.begin_interval_time begin_interval_time)
                measures(c.value_delta value_delta,
                         0 inst1,
                         0 inst2,
                         0 inst3)
                rules(inst1[any,any] = value_delta[i = 1, cv(begin_interval_time)],
                      inst2[any,any] = value_delta[i = 2, cv(begin_interval_time)],
                      inst3[any,any] = value_delta[i = 3, cv(begin_interval_time)])
              order by 1)
      where (inst1 >= 0 and inst2>= 0 and inst3>= 0);
begin
  for service_name_r in (select distinct service_name from dba_hist_service_stat) loop
    for service_cpu_r in cur_service(service_name_r.service_name) loop
      pipe row(service_cpu_t(service_name_r.service_name,service_cpu_r.begin_interval_time,service_cpu_r.inst1,service_cpu_r.inst2,service_cpu_r.inst3));
    end loop;
  end loop;
end;
/