You can use Oracle Database Resource Manager to manage the priority, across multiple parallel statement queues of multiple consumer groups, that parallel statements are dequeued from the parallel statement queue. The parallel statements for a particular consumer group are always dequeued in FIFO order. The directives
mgmt_p8 are used to determine which consumer group's parallel statement should be dequeued next. These directives are configured using the
UPDATE_PLAN_DIRECTIVE procedure in the
DBMS_RESOURCE_MANAGER PL/SQL package.
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 sets
mgmt_p1 to 70% for PQ_HIGH, 25% for PQ_MEDIUM, and 5% for PQ_LOW. This indicates that PQ_HIGH statements are dequeued next with a probability of 70% of the time, PQ_MEDIUM dequeued next with a probability of 25% of the time, and PQ_LOW dequeued next with a probability of 5% of the time.