Clicky Web Analytics


Posted By Ritesh Chhajer on Tuesday, February 08, 2011
10617 Views 1 Comments

Let us walk through the steps needed to setup Resource Manager.
1. Admin privilege:

Administering Resource Manager requires ADMINISTER_RESOURCE_MANAGER privilege. If we were to give this privilege to say user called 'SCOTT', we would run the following:
SQL> exec dbms_resource_manager_privs.grant_system_privilege(grantee_name=>'SCOTT',admin_option=>true);

2. Create a pending area:
The pending area is a temporary work area for Resource Manager configuration. The changes in the pending area are not visible until the pending area is submitted.
SQL> exec dbms_resource_manager.create_pending_area();

At any time, the changes can be abandoned using:
SQL> exec dbms_resource_manager.clear_pending_area();

3. Create Consumer Group:
Resource consumer groups are group of users, or sessions, that are grouped together based on their processing needs. Users can be assigned to more than one consumer group, but each user's active session can only be assigned to one resource consumer group at a time.
SQL> exec dbms_resource_manager.create_consumer_group(consumer_group=>'CG_DOP',comment=>'Limit the degree of parallelism');

The third argument is cpu_mth which is the resource allocation method. Default is ROUND-ROBIN which uses a round robin scheduler to ensure sessions are fairly executed. The option option is RUN-TO-COMPLETION which specifies that sessions with the largest active time are scheduled ahead of other sessions. We are keeping the default.

4. Map sessions to consumer groups:
You can configure the Database Resource Manager to automatically assign consumer groups to sessions by providing mappings between session attributes and consumer groups. Further, you can prioritize the mappings so as to indicate which mapping has precedence in case of conflicts. There are two types of session attributes: login attributes and runtime attributes. The login attributes are meaningful only at session login time, when the Database Resource Manager determines the initial consumer group of the session. In contrast, a session that has already logged in can later be reassigned to another consumer group based on its run-time attributes. We can use the set_consumer_group_mapping or set_consumer_group_mapping_pri procedures to add, delete, or modify entries that map sessions to consumer groups, based on the session's login and runtime attributes.
SQL> exec dbms_resource_manager.set_consumer_group_mapping(attribute => DBMS_RESOURCE_MANAGER.ORACLE_USER,value => 'SCOTT',consumer_group => 'CG_DOP');

ATTRIBUTE can be one of the following:
5. Add permissions to consumer groups:
In order to switch into a consumer group, user needs to have the permission.
SQL> exec dbms_resource_manager_privs.grant_switch_consumer_group(grantee_name => 'SCOTT', consumer_group => 'CG_DOP',grant_option => FALSE);

6. Create a Resource Plan
Resource plans specify the resource consumer groups belonging to the plan and contain directives for how resources are to be allocated among these groups.
SQL> exec dbms_resource_manager.create_plan (PLAN=> 'limit_parallelism', COMMENT => 'Limits parallelism to 4');

7. Add Resource Plan Directive:
Resource plan directives allocate resources among the resource consumer groups in the resource plan.
SQL> exec dbms_resource_manager.create_plan_directive(PLAN =>'limit_parallelism', GROUP_OR_SUBPLAN => 'CG_DOP', comment => 'limits the parallelism', PARALLEL_DEGREE_LIMIT_P1=> 4);

SQL>exec dbms_resource_manager.create_plan_directive(PLAN=> 'limit_parallelism', GROUP_OR_SUBPLAN =>'OTHER_GROUPS', comment=>'leave others alone', CPU_P1=>100);

8. Submit Pending Area:
Once you have configured the resource plan you can use the following procedure to persist your changes n the database.
SQL> exec dbms_resource_manager.submit_pending_area();

You can also validate your changes before submitting it.
SQL> exec dbms_resource_manager.validate_pending_area();

9. Enable Resource Plan:
Now that your resource plan is defined, enable it by setting the initialization parameter "resource_manager_plan" with your plan name.
SQL> alter system set resource_manager_plan = 'limit_parallelism' sid='*';
Note: In a RAC environment, Resource Manager manages each database instance independently. Each database instance can be configured with its own resource plan that reflects the applications it's running.

Now that we have seen the steps, let us take an example of controlling DOP(Degree of Parallelism) with all the steps put together.
Setup Example:
SQL> exec dbms_resource_manager.create_pending_area();

PL/SQL procedure successfully completed.

SQL> exec dbms_resource_manager.create_plan( plan => 'LIMIT_DOP', comment => 'Limit Degree of Parallelism');

PL/SQL procedure successfully completed.

SQL> exec dbms_resource_manager.create_consumer_group ( consumer_group => 'CG_DOP' , comment =>'Limit Degree of Parallelism');

PL/SQL procedure successfully completed.

SQL> exec dbms_resource_manager.create_plan_directive(plan => 'LIMIT_DOP', group_or_subplan => 'CG_DOP',comment => 'limits the parallelism', parallel_degree_limit_p1=> 4);

PL/SQL procedure successfully completed.

SQL> exec dbms_resource_manager.create_plan_directive(plan=> 'LIMIT_DOP', group_or_subplan =>'OTHER_GROUPS', comment=>'leave others alone',cpu_p1=>100);

PL/SQL procedure successfully completed.

PS: You need to run the aforementioned for OTHER_GROUPS as well otherwise you'll get error while submitting/validating the pending area as:
ORA-29377: consumer group OTHER_GROUPS is not part of top-plan LIMIT_DOP
ORA-06512: at "SYS.DBMS_RMIN", line 411
ORA-06512: at "SYS.DBMS_RESOURCE_MANAGER", line 444

SQL> exec dbms_resource_manager.set_consumer_group_mapping(attribute => DBMS_RESOURCE_MANAGER.ORACLE_USER,value => 'SCOTT',consumer_group => 'CG_DOP');

PL/SQL procedure successfully completed.

PS: Alternately, You can also map the user using the following:
SQL> exec dbms_resource_manager_privs.grant_switch_consumer_group(grantee_name=>'SCOTT',consumer_group=>'CG_DOP',grant_option=>FALSE);

PL/SQL procedure successfully completed.

SQL> exec dbms_resource_manager.set_initial_consumer_group(user => 'SCOTT',consumer_group =>'CG_DOP');

PL/SQL procedure successfully completed.

Let us say instead of user, you want to map service name you can use:
SQL> exec dbms_resource_manager.set_consumer_group_mapping(attribute => dbms_resource_manager.service_name,value => 'ETL_SVC',consumer_group => 'CG_DOP');

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> alter system set resource_manager_plan = 'LIMIT_DOP' sid='*';

Let us now connect using SCOTT user and validate our resource plan.
Verification Example:
SQL> select /*+ parallel(dba_segments,12) */ * from dba_segments,dba_extents;

From another session, check how many slaves were spawned.
Username     QC/Slave   Slave Set  QC SID Requested DOP Actual DOP    INST_ID    SID
------------ ---------- ---------- ------ ------------- ---------- ---------- ------
-      (Slave)    1          1058               2          2          1   1065
-      (Slave)    1          1058               2          2          2   1054
-      (Slave)    1          1056              12          4          1   1059
-      (Slave)    2          1056              12          4          1   1057
-      (Slave)    1          1056              12          4          1   1051
-      (Slave)    2          1056              12          4          1   1060
-      (Slave)    1          1056              12          4          1   1070
-      (Slave)    1          1056              12          4          1   1062
-      (Slave)    2          1056              12          4          1   1064
-      (Slave)    2          1056              12          4          1   1066
SYS          QC                    1058                                     1   1058
SCOTT      QC                    1056                                     1   1056

We can see from the above output that requested DOP was 12 but due to resource manager plan in place, the actual DOP was restricted to 4.

Let us now check few useful dictionary views and SQLs:
Dictionary Views Example:
SQL> show parameter resource_manager_plan

NAME                                 TYPE        VALUE
---------------------- -------- -----------
resource_manager_plan       string      LIMIT_DOP


------------- ---------------------------------------------- 
LIMIT_DOP       CG_DOP      4


------------- --------- ------------------
ORACLE_USER         SCOTT               CG_DOP

SQL> select username from dba_users where username='SCOTT';


Conclusion: Resource manager plan LIMIT_DOP is set for SCOTT user to restrict it's DOP to 4
Here is a list of useful dictionary views:

Now let us take an example of modifying DOP from 4 to 8 for LIMIT_DOP plan.
Update Plan Example:
SQL> exec dbms_resource_manager.create_pending_area();

PL/SQL procedure successfully completed.

SQL> exec DBMS_RESOURCE_MANAGER.UPDATE_PLAN_DIRECTIVE(plan => 'LIMIT_DOP', group_or_subplan => 'CG_DOP', new_comment => 'Limit DOP to 8', new_parallel_degree_limit_p1 => 8);

PL/SQL procedure successfully completed.

SQL> exec dbms_resource_manager.submit_pending_area();

PL/SQL procedure successfully completed.

There are some other procedures as well that can be used to delete the plan, switching the plan, etc.
DELETE_PLAN: Deletes the plan and it's directive
DELETE_PLAN_CASCADE: Deletes the plan including it's directive, subplans, consumer groups
DELETE_CONSUMER_GROUP: Deletes the consumer group
DELETE_PLAN_DIRECTIVE: Deletes the plan directive
SWITCH_CONSUMER_GROUP_FOR_SESS: Changes the consumer group for a session
SQL> select sid, serial# from v$session where username='SCOTT';
SID        SERIAL#
----       --------
1057      10583

SQL> exec dbms_resource_manager.create_pending_area();

PL/SQL procedure successfully completed.

SQL> exec dbms_resource_manager.SWITCH_CONSUMER_GROUP_FOR_SESS(1057,10583,'DEFAULT_CONSUMER_GROUP');

PL/SQL procedure successfully completed.

SQL> exec dbms_resource_manager.submit_pending_area();

PL/SQL procedure successfully completed.

SWITCH_CONSUMER_GROUP_FOR_USER: Changes consumer group for all sessions for given user
SWITCH_PLAN_PROCEDURE: Sets the current resource manager plan
UPDATE_CONSUMER_GROUP: Updates entries which define resource consumer groups.
UPDATE_PLAN: Updates entries which define the resource plan

So you see, Oracle Database Resource Manager is pretty handy to manage the workload and the resources. This way DBA can make sure that the critical processes do not starve for resources.

My sincere thanks to Arpan and Mukesh for their useful contributions in testing the resource manager and documenting the results.

Rants & Raves Minimize

  • Gravatar
    Kowolski Tuesday, September 01, 2015 at 6:57 PM

    Appreciate your effort in writing such a clear blog about resource manager.
    Keep up the excellent work.

Recommended Oracle DBA Books Minimize


Tag Cloud Minimize

Archive Posts Minimize

    This posting is provided "AS IS" with no warranties, and confers no rights. You assume all risk for your use.

    This posting has nothing to do with my present or past employer.