Practice: Setting Up Database Resource Manager

 

This practice uses resource manager to create resource plans, consumer groups, plan directives, and assigning groups to plans.

 

 

Assumptions

 

·        An Oracle 9i database is up and running.

 

Instructions

 

1.      Create a pending area for resource manager to use to create consumer groups and plans. Create a plan called “SINGLE_LEVEL_PLAN” and three consumer groups called OLTP_Group, BATCH_Group, and ADHOC_Group.

 

SQL> exec dbms_resource_manager.create_pending_area();

 

PL/SQL procedure successfully completed.

 

SQL> exec dbms_resource_manager.create_plan(plan=>'SINGLE_LEVEL_PLAN',comme

nt=>'Resource plan/method for Single level sample');

 

PL/SQL procedure successfully completed.

 

SQL> exec dbms_resource_manager.create_consumer_group(consumer_group => 'OL

TP_Group', comment => 'Resource consumer group/method for online users sess

ions');

 

PL/SQL procedure successfully completed.

 

SQL> exec dbms_resource_manager.create_consumer_group(consumer_group => 'BA

TCH_Group', comment => 'Resource consumer group/method for users sessions w

ho run batch jobs');

 

PL/SQL procedure successfully completed.

 

SQL> exec dbms_resource_manager.create_consumer_group(consumer_group => 'AD

HOC_Group', comment => 'Resource consumer group/method for users sessions w

ho execute Ad-Hoc Queries');

 

PL/SQL procedure successfully completed.

 

 

2.      Define four plan directives; the first assigning cpu_p1 of 80 to OLTP_Group, the second assigning cpu_p1 of 10 to BATCH_Group, the third assigning cpu_p1 of 10 to ADHOC_Group, and the forth cpu_p1 of 0 to OTHER_GROUPS.

 

SQL> exec dbms_resource_manager.create_plan_directive(plan => 'SINGLE_LEVEL

_PLAN', group_or_subplan => 'OLTP_Group', comment => 'Online day users sess

ions at level 1', cpu_p1 => 80, parallel_degree_limit_p1 => 0);

 

PL/SQL procedure successfully completed.

 

SQL> exec dbms_resource_manager.create_plan_directive(plan => 'SINGLE_LEVEL

_PLAN', group_or_subplan => 'BATCH_Group', comment => 'batch day users sess

ions at level 1', cpu_p1 => 10, parallel_degree_limit_p1 => 10);

 

PL/SQL procedure successfully completed.

 

SQL> exec dbms_resource_manager.create_plan_directive(plan => 'SINGLE_LEVEL

_PLAN', group_or_subplan => 'ADHOC_Group', comment => 'ADHOC day users sess

ions at level 1', cpu_p1 => 10, parallel_degree_limit_p1 => 5);

 

PL/SQL procedure successfully completed.

 

SQL> exec dbms_resource_manager.create_plan_directive(plan => 'SINGLE_LEVEL

_PLAN', group_or_subplan => 'OTHER_GROUPS', comment => 'OTHER_GROUPS day us

ers sessions at evel 1', cpu_p1 => 0, parallel_degree_limit_p1 => 0);

 

PL/SQL procedure successfully completed.

 

 

3.      Validate and submit the pending area.

 

SQL> exec dbms_resource_manager.validate_pending_area();

 

PL/SQL procedure successfully completed.

 

SQL> exec dbms_resource_manager.submit_pending_area();

 

PL/SQL procedure successfully completed.

 

SQL> exec dbms_resource_manager.create_pending_area();

 

 

 

This completes this practice

Practice: Assigning Users to Resource Manager Groups

 

This practice uses resource manager to assign users to the different resource groups.

 

 

Assumptions

 

·        An Oracle 9i database is up and running.

 

Instructions

 

1.      Grant the ability for users OLTP, ADHOC, and BATCH to switch consumer groups using the “dbms_resource_manager_privs.grant_switch_consumer_group” procedure.

 

SQL> exec dbms_resource_manager_privs.grant_switch_consumer_group(grantee_n

ame => 'ADHOC', consumer_group => 'ADHOC_Group', grant_option => FALSE);

 

PL/SQL procedure successfully completed.

 

SQL> exec dbms_resource_manager_privs.grant_switch_consumer_group(grantee_n

ame => 'OLTP', consumer_group => 'OLTP_Group', grant_option => FALSE);

 

PL/SQL procedure successfully completed.

 

SQL> exec dbms_resource_manager_privs.grant_switch_consumer_group(grantee_n

ame => 'BATCH', consumer_group => 'BATCH_Group', grant_option => FALSE);

 

PL/SQL procedure successfully completed.

 

 

2.      Set the initial consumers groups for each user to the corresponding consumer group.

 

SQL> exec dbms_resource_manager.set_initial_consumer_group(user => 'ADHOC',

 consumer_group => 'ADHOC_Group');

 

PL/SQL procedure successfully completed.

 

SQL> exec dbms_resource_manager.set_initial_consumer_group(user => 'OLTP',

consumer_group => 'OLTP_Group');

 

PL/SQL procedure successfully completed.

 

SQL> exec dbms_resource_manager.set_initial_consumer_group(user => 'BATCH',

 consumer_group => 'BATCH_Group');

 

PL/SQL procedure successfully completed.

 

 

3.      Validate and submit the pending area.

 

SQL> exec dbms_resource_manager.validate_pending_area();

 

PL/SQL procedure successfully completed.

 

SQL> exec dbms_resource_manager.submit_pending_area();

 

PL/SQL procedure successfully completed.

 

SQL> exec dbms_resource_manager.create_pending_area();

 

 

 

This completes this practice

1