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.<\/p>\n
You can use what is below to calculate it. It should be noted that this is for a three node cluster.<\/p>\n
\r\ncreate or replace type service_cpu_t as object (service_name varchar2(100),\r\n begin_interval_time varchar2(30), \r\n cpu1 number, cpu2 number, cpu3 number)\r\n\/\r\n\r\ncreate or replace type service_cpu_o as table of service_cpu_t\r\n\/\r\n\r\ncreate or replace function service_cpu return service_cpu_o pipelined\r\n is\r\n cursor cur_service(p_service_name varchar2) is\r\n select *\r\n from (select distinct begin_interval_time,\r\n trunc(inst1\/10000\/14400) inst1,\r\n trunc(inst2\/10000\/14400) inst2,\r\n trunc(inst3\/10000\/14400) inst3\r\n from (select to_char(begin_interval_time,'YYYY_MM_DD HH24:MI') begin_interval_time,\r\n dhss.instance_number,service_name,\r\n value - lag(value, 1, 0) over (order by dhss.instance_number, dhss.snap_id) as value_delta\r\n from dba_hist_snapshot dhs,\r\n dba_hist_service_stat dhss\r\n where dhs.snap_id = dhss.snap_id\r\n and dhs.instance_number = dhss.instance_number\r\n and service_name = p_service_name\r\n and stat_name = 'DB CPU'\r\n order by 1) c\r\n model\r\n ignore nav\r\n dimension by(c.instance_number i,\r\n c.begin_interval_time begin_interval_time)\r\n measures(c.value_delta value_delta,\r\n 0 inst1,\r\n 0 inst2,\r\n 0 inst3)\r\n rules(inst1[any,any] = value_delta[i = 1, cv(begin_interval_time)],\r\n inst2[any,any] = value_delta[i = 2, cv(begin_interval_time)],\r\n inst3[any,any] = value_delta[i = 3, cv(begin_interval_time)])\r\n order by 1)\r\n where (inst1 >= 0 and inst2>= 0 and inst3>= 0);\r\nbegin\r\n for service_name_r in (select distinct service_name from dba_hist_service_stat) loop\r\n for service_cpu_r in cur_service(service_name_r.service_name) loop\r\n 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));\r\n end loop;\r\n end loop;\r\nend;\r\n\/\r\n<\/pre>\n","protected":false},"excerpt":{"rendered":"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…<\/p>\n