Using Resource Manager to Manage Degree of Parallelism

I was asked to implement a resource manager to limit the degree of parallelism for all the users except a particular user who does more batch processing.
This is what I did:

BEGIN

DBMS_RESOURCE_MANAGER.CLEAR_PENDING_AREA();

DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA();

DBMS_RESOURCE_MANAGER.CREATE_PLAN(PLAN => 'DOP_PLAN',

COMMENT => 'Plan to limit degree of parallelism');

DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP ( consumer_group => 'NOLIMIT_DOP' , comment =>'Batch users consumer group');

DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE (plan => 'DOP_PLAN', group_or_subplan => 'NOLIMIT_DOP',

comment => 'NO limits to parallelism');

DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE (plan => 'DOP_PLAN', group_or_subplan => 'OTHER_GROUPS',

comment => 'Limits use of parallelism',parallel_degree_limit_p1=> 4);

DBMS_RESOURCE_MANAGER.set_consumer_group_mapping(attribute => DBMS_RESOURCE_MANAGER.ORACLE_USER,value => 'BATCH_USER',consumer_group => 'NOLIMIT_DOP');

DBMS_RESOURCE_MANAGER.validate_pending_area();

DBMS_RESOURCE_MANAGER.submit_pending_area();

END;

/

exec DBMS_RESOURCE_MANAGER_PRIVS.grant_switch_consumer_group('BATCH_USER','NOLIMIT_DOP',false);

exec DBMS_RESOURCE_MANAGER.set_initial_consumer_group(user => 'BATCH_USER',consumer_group =>'NOLIMIT_DOP');

alter system set resource_manager_plan = 'DOP_PLAN' scope=both;

-----------------------------------------------------------------------------------------------------

Ok, this is what each line does:

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.

DBMS_RESOURCE_MANAGER.CLEAR_PENDING_AREA();
DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA();

Create a resource plan which called 'DOP_PLAN" which will contain the resource consumer group.

DBMS_RESOURCE_MANAGER.CREATE_PLAN(PLAN => 'DOP_PLAN',

COMMENT => 'Plan to limit degree of parallelism');

Create consumer group called 'NOLIMT_DOP'

DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP ( consumer_group => 'NOLIMIT_DOP' , comment =>'Batch users consumer group');

Create plan directive to this group without any limits. This resource group will not have limit on degree of parallelism

DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE (plan => 'DOP_PLAN', group_or_subplan => 'NOLIMIT_DOP',

comment => 'NO limits to parallelism');

All the other users who are part of OTHER_GROUPS will have a degree of parallelism limit to 4.

DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE (plan => 'DOP_PLAN', group_or_subplan => 'OTHER_GROUPS',

comment => 'Limits use of parallelism',parallel_degree_limit_p1=> 4);

Add the user called BATCH_USER to the consumer group NOLIMIT_DOP.

DBMS_RESOURCE_MANAGER.set_consumer_group_mapping(attribute => DBMS_RESOURCE_MANAGER.ORACLE_USER,value => 'BATCH_USER',consumer_group => 'NOLIMIT_DOP');

Submit the changes.

DBMS_RESOURCE_MANAGER.validate_pending_area();

DBMS_RESOURCE_MANAGER.submit_pending_area();

Ensure the BATCH_USER user has permission to access NOLIMIT_DOP resource group and that is the initial group set for the user.

exec DBMS_RESOURCE_MANAGER_PRIVS.grant_switch_consumer_group('BATCH_USER','NOLIMIT_DOP',false);

exec DBMS_RESOURCE_MANAGER.set_initial_consumer_group(user => 'BATCH_USER',consumer_group =>'NOLIMIT_DOP');

Enable the resource plan

alter system set resource_manager_plan = 'DOP_PLAN' scope=both;

----------------------------------------------------------------------------------------

In order to see it really works, this is what I did:

The database has maximum parallel servers defined as

SQL> show parameter parallel_max

NAME                                 TYPE        VALUE
------------------------------------ ----------- ----------------------
parallel_max_servers                 integer     24

Connected as user BATCH_USER and issue the following command:

select /*+ parallel(dba_segments,10) */ * from dba_segments,dba_extents;

From another session I issue the following to see how many parallel processes did the user get and how much was requested:

SQL> select A.USERNAME,B.SID,b.qcsid,b.DEGREE,b.REQ_DEGREE from v$session a,v$
px_session b where a.sid=b.qcsid and a.serial#=b.qcserial# AND a.STATUS='ACTIVE'
 order by 1;

USERNAME        SID      QCSID     DEGREE REQ_DEGREE
---------- ---------- ---------- ---------- ----------
BATCH_USER        112        116         10         10
BATCH_USER        103        116         10         10
BATCH_USER        111        116         10         10
BATCH_USER        124        116         10         10
BATCH_USER        114        116         10         10
BATCH_USER        105        116         10         10
BATCH_USER        106        116         10         10
BATCH_USER        113        116         10         10
BATCH_USER        119        116         10         10
BATCH_USER        144        116         10         10
BATCH_USER        118        116         10         10
BATCH_USER        110        116         10         10
BATCH_USER        115        116         10         10
BATCH_USER        107        116         10         10
BATCH_USER        130        116         10         10
BATCH_USER        109        116         10         10
BATCH_USER        108        116         10         10
BATCH_USER        104        116         10         10
BATCH_USER        142        116         10         10
BATCH_USER        149        116         10         10

From above you can see that the requested degree was 10 and the user got 10.

Now if I connect as another user other than BATCH_USER and request for 10 parallel processes, I should not get them all. Well lets see. I connected as perfstat user and issued the following:

select /*+ parallel(dba_segments,10) */ * from dba_segments,dba_extents;

Then connected to another session to check how many parallel processes were given:

SQL> select a.USERNAME,B.SID,b.qcsid,b.DEGREE,b.REQ_DEGREE from v$session a,v$px
_session b where a.sid=b.qcsid and a.serial#=b.qcserial# AND a.STATUS='ACTIVE' o
rder by 1;

USERNAME                              SID      QCSID     DEGREE REQ_DEGREE
------------------------------ ---------- ---------- ---------- ----------
SYSTEM                                116        121          4         10
SYSTEM                                115        121          4         10
SYSTEM                                108        121          4         10
SYSTEM                                142        121          4         10
SYSTEM                                104        121          4         10
SYSTEM                                118        121          4         10
SYSTEM                                109        121          4         10
SYSTEM                                130        121          4         10

So you can see that the user is only getting 4 parallel processes but it requested 10.

Hence, the resource plan is working the way I wanted it to.