|
Tip of the Week
Tip for Week of December 25, 2006
Collecting Percentage of CPU Used by Consumer Group
This tip comes from
Marco Gilbert, a DBA at MRQ, in Quebec, Canada.
This tip is for people who use Oracle Resource Manager in Oracle Database 10g to manage CPU usage by consumer group.
Oracle provides the V$RSRC_CONSUMER_GROUP view where you can see a CONSUMED_CPU_TIME column that contains the cumulative amount of CPU time consumed by all sessions in the consumer group.
This column is cumulative, however, so it does not tell you anything about a specific period of time. So you cannot use it to know which consumer group consumed the most CPU in the last 5 minutes. You cannot collect a history of the CPU used by each group for specific period of time either.
So here is what you can do to get this information.
First of all, you need to create a snapshot of the V$RSRC_CONSUMER_GROUP because you cannot use the "as of timestamp" clause on a V$ view. Refresh this snapshot every minute.
create materialized view MV_RSRC_CONSUMER_GROUP
refresh FORCE
start with sysdate
next sysdate + 1 / 1440
as
SELECT name, consumed_cpu_time from v$rsrc_consumer_group;
Then, to get the %CPU used by each group in the last 5 minutes, use this query:
select b.name,
100 * (decode(sign(b.consumed_cpu_time - a.consumed_cpu_time),
-1, b.consumed_cpu_time,
b.consumed_cpu_time - a.consumed_cpu_time) /
decode(sign(c.cpu_tot_current - d.cpu_tot_before),
-1, c.cpu_tot_current,
c.cpu_tot_current - d.cpu_tot_before)) "CPU"
from MV_RSRC_CONSUMER_GROUP as of timestamp (systimestamp - interval '5' minute) a,
MV_RSRC_CONSUMER_GROUP b,
(select sum(consumed_cpu_time) "CPU_TOT_CURRENT"
from MV_RSRC_CONSUMER_GROUP) c,
(select sum(consumed_cpu_time) "CPU_TOT_BEFORE"
from MV_RSRC_CONSUMER_GROUP as of timestamp (systi! mestamp - interval '5' minute)) d
where b.name = a.name(+);
You can then use that query as a "User-Defined SQL Metrics" in Grid Control to collect a history of the CPU percentage used by each consumer group! You could modify the query easily to get the amount of CPU used instead of a percentage if you prefer.
This is really useful if you want to monitor your consumer group and keep the history for a column that is cumulative in the V$RSRC_CONSUMER_GROUP. Just include it in the materialized view and define a "User-Defined SQL Metric" in Grid Control to keep historical data.
|
Bookmark
