About Managing Parallel Statement Queuing with Oracle Database Resource Manager

By default, the parallel statement queue operates as a first-in, first-out queue. By configuring and setting a resource plan, you can control the order in which parallel statements are dequeued and the number of parallel execution servers used by each workload or consumer group.

Oracle Database Resource Manager operates based on the concept of consumer groups and resource plans. Consumer groups identify groups of users with identical resource privileges and requirements. A resource plan consists of a collection of directives for each consumer group which specify controls and allocations for various database resources, such as parallel servers. For multitenant container databases (CDBs) and pluggable databases (PDBs), the order of the parallel statement queue is managed by the directive called shares.

A resource plan is enabled by setting the RESOURCE_MANAGER_PLAN parameter to the name of the resource plan.

You can use the directives described in the following sections to manage the processing of parallel statements for consumer groups when the parallel degree policy is set to AUTO.

• About Managing the Order of the Parallel Statement Queue

• About Limiting the Parallel Server Resources for a Consumer Group

• Specifying a Parallel Statement Queue Timeout for Each Consumer Group

• Specifying a Degree of Parallelism Limit for Consumer Groups

• Critical Parallel Statement Prioritization

• About Managing Parallel Statement Queuing for CDBs and PDBs

• A Sample Scenario for Managing Statements in the Parallel Queue

In all cases, the parallel statement queue of a given consumer group is managed as a single queue on an Oracle RAC database. Limits for each consumer group apply to all sessions across the Oracle RAC database that belong to that consumer group. The queuing of parallel statements occurs based on the sum of the values of the PARALLEL_SERVERS_TARGET initialization parameter across all database instances.