I/O Resource Management and Instance Caging - Oracle Exadata Recipes: A Problem-Solution Approach - page 498

1. Create a pending area, resource plan, consumer groups, and resource plan directives. In

the following, we are creating a resource plan called visx_plan , creating three consumer
groups, and assigning 50%, 40%, and 10% CPU utilizations to the CG_SHIPPING , CG_FINANCE ,
and CG_REPORTING consumer groups, respectively:

SYS @ visx1> begin
dbms_resource_manager.create_pending_area();
dbms_resource_manager.create_plan(plan=>'visx_plan',
comment=>'VISX Plan');
dbms_resource_manager.create_consumer_group(consumer_group=>'CG_SHIPPING',
comment=>'CG_SHIPPING');
dbms_resource_manager.create_consumer_group(consumer_group=>'CG_FINANCE',
comment=>'CG_FINANCE');
dbms_resource_manager.create_consumer_group(consumer_group=>'CG_REPORTING',
comment=>'CG_REPORTING');
dbms_resource_manager.create_plan_directive(plan=>'visx_plan',
comment=>'CG_SHIPPING_50_L1',group_or_subplan=>'CG_SHIPPING', mgmt_p1=>50);
dbms_resource_manager.create_plan_directive(plan=>'visx_plan',
comment=>'CG_FINANCE_40_L1',group_or_subplan=>'CG_FINANCE',mgmt_p1=>40);
dbms_resource_manager.create_plan_directive(plan=>'visx_plan',
comment=>'CG_REPORTING_10_L1',group_or_subplan=>'CG_REPORTING',mgmt_p1=>10);
dbms_resource_manager.create_plan_directive(plan=>'visx_plan',
comment=>'OTHER_GROUPS_L2',group_or_subplan=>'OTHER_GROUPS',
mgmt_p1=>0,mgmt_p2=>100);
dbms_resource_manager.validate_pending_area();
dbms_resource_manager.submit_pending_area();
end;
/
PL/SQL procedure successfully completed.
SYS @ visx1>

Assign sessions to the consumer groups. In the following example, we are assigning a USR1
Oracle user to the CG_SHIPPING consumer group, USR2 to CG_FINANCE , and USR3 to the
CG_REPORTING consumer group:

2.

SYS @ visx1> begin
dbms_resource_manager.create_pending_area();
dbms_resource_manager.set_consumer_group_mapping(dbms_resource_manager.oracle_user,
'USR1','CG_SHIPPING');
dbms_resource_manager.set_consumer_group_mapping(dbms_resource_manager.oracle_user,
'USR2','CG_FINANCE');
dbms_resource_manager.set_consumer_group_mapping(dbms_resource_manager.oracle_user,
'USR3','CG_REPORTING');
dbms_resource_manager_privs.grant_switch_consumer_group(
grantee_name=>'USR1',consumer_group=>'CG_SHIPPING',grant_option=>TRUE);
dbms_resource_manager_privs.grant_switch_consumer_group(
grantee_name=>'USR2',consumer_group=>'CG_FINANCE',grant_option=>TRUE);
dbms_resource_manager_privs.grant_switch_consumer_group(
grantee_name=>'USR3',consumer_group=>'CG_REPORTING',grant_option=>TRUE);