You can use Oracle Database Resource Manager to the limit the degree of parallelism for specific consumer groups. Using Oracle Database Resource Manager you can map parallel statement sessions to different consumer groups that each have specific limits for the degree of parallelism in a resource plan.
To manage the limit of parallelism in consumer groups, use the
parallel_degree_limit_p1 parameter with the
CREATE_PLAN_DIRECTIVE procedure in the
DBMS_RESOURCE_MANAGER package or the
new_parallel_degree_limit_p1 parameter with the
UPDATE_PLAN_DIRECTIVE procedure in the
DBMS_RESOURCE_MANAGER package. The
new_parallel_degree_limit_p1 parameters specify a limit on the degree of parallelism for any operation.
For example, you can create the PQ_HIGH, PQ_MEDIUM, and PQ_LOW consumer groups and map parallel statement sessions to these consumer groups based on priority. You then create a resource plan that specifies degree of parallelism limits so that the PQ_HIGH limit is set to 16, the PQ_MEDIUM limit is set to 8, and the PQ_LOW limit is set to 2.
The degree of parallelism limit is enforced, even if
PARALLEL_DEGREE_POLICY is not set to