04 October 2013

Oracle Resource Manager: implementing modified simple plan (NOTES)

I set up a Resource Manager recently to manage the CPU allocation on our "Schema as a Service" RAC system. Each application purchases a slice of the system's CPU allocation, and we are managing and/or monitoring the allocation through RM.

Create a simple plan 

BEGIN
  DBMS_RESOURCE_MANAGER.CREATE_SIMPLE_PLAN(SIMPLE_PLAN = 'P01_PLAN',
   CONSUMER_GROUP1 = 'ORA$AUTOTASK_SUB_PLAN', GROUP1_PERCENT = 5,
   CONSUMER_GROUP2 = 'ORA$DIAGNOSTICS', GROUP2_PERCENT = 5,
   CONSUMER_GROUP3 = 'OLTP', GROUP3_PERCENT = 14,
   CONSUMER_GROUP4 = 'Small apps',   GROUP4_PERCENT = 7,
   CONSUMER_GROUP5 = 'Medium Apps', GROUP5_PERCENT = 7,
   CONSUMER_GROUP6 = 'DSS', GROUP6_PERCENT = 7
);
END;
/

This creates 3 levels of consumer groups. 
Percentage were calculated based on the number of applications in each group.

Assign services to the consumer groups :
exec dbms_resource_manager.clear_pending_area;
exec dbms_resource_manager.create_pending_area;

 exec dbms_resource_manager.set_consumer_group_mapping( attribute = dbms_resource_manager.service_name, -
value = 'DBSRVP01', consumer_group = 'MEDIUM APPS');
exec dbms_resource_manager.set_consumer_group_mapping( attribute = dbms_resource_manager.service_name, -
value = 'DBSRVP02', consumer_group = 'SMALL APPS' );
exec dbms_resource_manager.set_consumer_group_mapping( attribute = dbms_resource_manager.service_name, -
value = 'DBSRVP03', consumer_group = 'SMALL APPS');
exec dbms_resource_manager.set_consumer_group_mapping( attribute = dbms_resource_manager.service_name, -
value = 'DBSRVP04', consumer_group = 'OLTP');
exec dbms_resource_manager.set_consumer_group_mapping( attribute = dbms_resource_manager.service_name, -
value = 'DBSRVP05', consumer_group = 'OLTP');
exec dbms_resource_manager.set_consumer_group_mapping( attribute = dbms_resource_manager.service_name, -
value = 'DBSRVP06', consumer_group = 'OLTP');
exec dbms_resource_manager.set_consumer_group_mapping( attribute = dbms_resource_manager.service_name, -
value = 'DBSRVP07', consumer_group = 'OLTP');
exec dbms_resource_manager.set_consumer_group_mapping( attribute = dbms_resource_manager.service_name, -

 value = 'DBSRVP08', consumer_group = 'DSS');

Put a hard core limit on Medium Apps:
EXEC dbms_resource_manager.create_plan_directive(-
plan='P01_PLAN', group_or_subplan= 'Medium Apps', -
max_utilization_limit = 7, comment= 'Put a hard core on Medium apps');
exec dbms_resource_manager.submit_pending_area;

--Allow privileges
exec dbms_resource_manager_privs.grant_switch_consumer_group( -
grantee_name = 'PUBLIC', consumer_group = 'SMALL APPS', grant_option = FALSE);
exec dbms_resource_manager_privs.grant_switch_consumer_group( -
grantee_name = 'PUBLIC', consumer_group = 'MEDIUM APPS', grant_option = FALSE);
exec dbms_resource_manager_privs.grant_switch_consumer_group( -
grantee_name = 'PUBLIC', consumer_group = 'OLTP', grant_option = FALSE);
exec dbms_resource_manager_privs.grant_switch_consumer_group( -

 grantee_name = 'PUBLIC', consumer_group = 'DSS', grant_option = FALSE);

Manage the RM:
exec dbms_resource_manager_privs.grant_system_privilege(grantee_name = 'USERNAME', admin_option = true);

Switch the plan on:
alter system set resource_manager_plan = 'P01_PLAN' sid = '*';

Switch the plan off:
alter system set resource_manager_plan = 'INTERNAL_PLAN' sid = '*';

MonitoringOverview

Select the current plan:
select name, cpu_managed from v$rsrc_plan where is_top_plan = 'TRUE';

Check which plans have been enabled/disabled in the past:
SELECT name plan_name,
to_char(start_time, 'DD-MON-YY HH24:MM') start_time,
to_char(end_time, 'DD-MON-YY HH24:MM') end_time, window_name
FROM v$rsrc_plan_history order by 2 desc;

To check to which resource group the session belongs to:
select sid, username, service_name, resource_consumer_group from v$session
where RESOURCE_CONSUMER_GROUP!='_ORACLE_BACKGROUND_GROUP_';

CPU Monitoring

Throthling:
select to_char(begin_time, 'HH:MI') time,
 60 * (select value from v$osstat where stat_name = 'NUM_CPUS') total,
 60 * (select value from v$parameter where name = 'cpu_count') db_total,
 sum(cpu_consumed_time) / 1000 consumed,
 sum(cpu_wait_time) / 1000 throttled
from gv$rsrcmgrmetric_history
group by begin_time order by begin_time;

Waits:

Throttling by Resource Manager can be monitored by the wait event “resmgr:cpu quantum”, or scheduler” wait class. These waits can be seen by generating an AWR report and examining the “Foreground Wait Events”.

“dbtime_in_wait” shows the percentage of database time spent in Resource Manager waits.
“time_waited” shows the actual wait time in microseconds.

select to_char(h.begin_time, 'HH:MI') time,
 h.average_waiter_count, h.dbtime_in_wait, h.time_waited
from v$waitclassmetric_history h, v$system_wait_class c
where h.wait_class_id = c.wait_class_id and c.wait_class = ' SCHEDULER'
order by h.begin_time;

CPU

Actual CPU utilization per Consumer Group:
select to_char(begin_time, 'HH:MI') time,
consumer_group_name,
 60 * (select value from v$osstat where stat_name = 'NUM_CPUS') total,
 60 * (select value from v$parameter where name = 'cpu_count') db_total,
cpu_consumed_time / 1000 consumed,
cpu_consumed_time / (select value from v$parameter where name =
 'cpu_count') / 600 cpu_utilization,
cpu_wait_time / 1000 throttled
from v$rsrcmgrmetric_history
order by begin_time;
"throttled" should be very low or zero.

Check current resource groups, CPU consumption, queing for CPU (queue length should be zero or close):
SELECT name, active_sessions, queue_length,
  consumed_cpu_time, cpu_waits, cpu_wait_time
  FROM v$rsrc_consumer_group;

The same, just sessions queing for the CPU time:
SELECT se.sid sess_id, co.name consumer_group,
 se.state, se.consumed_cpu_time cpu_time, se.cpu_wait_time, se.queued_time
 FROM v$rsrc_session_info se, v$rsrc_consumer_group co
 WHERE se.current_consumer_group_id = co.id;

Privileges

If the session is not in the resource group it should be, first check that it has privileges to switch to that group and when it mapped:
select grantee, granted_group from DBA_RSRC_CONSUMER_GROUP_PRIVS
order by granted_group;

select r.sid,
r.mapped_consumer_group,
r.mapping_attribute,
c.consumer_group original_consumer_group
from v$rsrc_session_info r, dba_rsrc_consumer_groups c
where r.orig_consumer_group_id = c.consumer_group_id;

--Any SIDs that switched from one group to another:
select r.sid,
c1.consumer_group original_consumer_group,
c2.consumer_group current_consumer_group
from v$rsrc_session_info r, dba_rsrc_consumer_groups c1,
dba_rsrc_consumer_groups c2
where r.orig_consumer_group_id = c1.consumer_group_id
and r.current_consumer_group_id = c2.consumer_group_id
and r.orig_consumer_group_id != r.current_consumer_group_id;


List groups that have AUTO switch properties:
select plan, group_or_subplan, switch_time, switch_io_megabytes, switch_io_reqs
from dba_rsrc_plan_directives
where plan in (select name from v$rsrc_plan)
 and (switch_time is not null
 or switch_io_megabytes is not null
 or switch_io_reqs is not null);


Other views in DBA_RSRC% and (G)V$RSRC%


// posted by Tatjana (Tanja) Kane @ 10/04/2013
Comments: Post a Comment



<< Home

This page is powered by Blogger. Isn't yours?