Oracle Resource manager

Le  but  est   de faire  un repartition des  resources  CPU  selon  le  service avec  lequel  on se connecte comme ceci :
 

groupe	service_name	%CPU
Sale_grp	sale_srv	50
Market_grp	market_srv	20
Develop_grp	develop_srv	20
Other_Goups		10

 Si  un user  se connect  au  service  Sale_srv il aura  potentiellement 50% de CPU.

1-Il  faut  donner  les  droits d adminstrer resource manager :

exec dbms_resource_manager_privs.grant_system_privilege( grantee_name =>'tanos',admin_option => true);

2-initialisation de la  pending  area :

3-on se connecte  et  on  crée  les  groupes :

 exec dbms_resource_manager.clear_pending_area();

 exec dbms_resource_manager.create_pending_area();                                                   

exec dbms_resource_manager.create_consumer_group( 'SALE_GRP', 'Sessions for the Sales Application');            

exec dbms_resource_manager.create_consumer_group( 'MARKET_GRP', 'Sessions for the MARKET Application');            

exec dbms_resource_manager.create_consumer_group( 'DEVELOP_GRP', 'Sessions for the DEVELOP Application');            

4-On  fait  le  mapping  user  session et  service_name :

 exec dbms_resource_manager.set_consumer_group_mapping( attribute => dbms_resource_manager.service_name, value => 'SALE_SRV', consumer_group => 'SALE_GRP');

exec dbms_resource_manager.set_consumer_group_mapping( attribute => dbms_resource_manager.service_name, value => 'MARKET_SRV', consumer_group => 'MARKET_GRP');

exec dbms_resource_manager.set_consumer_group_mapping( attribute => dbms_resource_manager.service_name, value => 'DEVELOP_SRV', consumer_group => 'DEVELOP_GRP');

5-On associe   un  user  a  un groupe   et   donc  a   un service .

Notre user  de  test  est PP

Pour que  PP  fasse  partie  du  directive plan  il faudra 2  condition que  PP se  connecte  a  la  db  en utilisant  le  service_name  Sale _srv.

exec dbms_resource_manager_privs.grant_switch_consumer_group( grantee_name => 'PP', consumer_group => 'SALE_GRP',grant_option => FALSE);

 6-On cree  le  plan :

exec dbms_resource_manager.create_plan( 'GREAT_BREAD_PLAN', 'Plan for appli great bread');

7 On cree  les directives associées anotre plan :

begin

 dbms_resource_manager.create_plan_directive(

PLAN => 'GREAT_BREAD_PLAN',

GROUP_OR_SUBPLAN => 'SALE_GRP',

Comment => 'Percentage of CPU for SALE',

mgmt_p1 => 50,

max_utilization_limit =>65);

 dbms_resource_manager.create_plan_directive(

PLAN => 'GREAT_BREAD_PLAN',

GROUP_OR_SUBPLAN => 'MARKET_GRP',

Comment => 'Percentage of CPU for MARKET',

mgmt_p1 => 20,

max_utilization_limit =>40);

 dbms_resource_manager.create_plan_directive(

PLAN => 'GREAT_BREAD_PLAN',

GROUP_OR_SUBPLAN => 'DEVELOP_GRP',

Comment => 'Percentage of CPU for DEVELOP',

mgmt_p1 => 20);

dbms_resource_manager.create_plan_directive(

PLAN => 'GREAT_BREAD_PLAN',

GROUP_OR_SUBPLAN => 'OTHER_GROUPS',

Comment => 'Percentage of CPU for OTHER',

mgmt_p1 => 10);

end;

7-On valide est on soumet :

On   valide  et  on soumet  la  pending area

exec dbms_resource_manager.validate_pending_area();

exec dbms_resource_manager.submit_pending_area();

8-On associe   le  plan   au  parameter resource_manager_plan

alter system set resource_manager_plan = 'GREAT_BREAD_PLAN' sid='*';