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.
