Database Reference
In-Depth Information
Prioritizing Parallel Statement Queues (12c)
The Parallel Statement Queuing feature works in a strict First In- First Out (FIFO) model in Database versions up to
11.2. In version 12c, Database Resource Manager can be utilized to create multiple parallel statement queues and
prioritize the next parallel statement execution. Parallel statements from a higher-priority parallel statement queue
are dequeued for the next execution. The parallel statement queue feature is enhanced to have multiple statement
queues.
It is easier to understand priority among parallel statement queues using an example. Consider that you want to
design a queuing scheme with the following objectives:
1.
SYS_GROUP should have highest priority and any statements in this consumer group must
be dequeued first.
2.
If no session is waiting from the SYS_GROUP queue, then the SQL statement should be
dequeued in the order of PX_CRITICAL, PX_HIGH, PX_MEDIUM, and PX_LOW queues.
The following code will implement the designed priority objective. In this example, four new consumer groups
are created and priority is assigned among those consumer groups. First, a pending area is created to make changes to
resource consumer groups. The next step creates a resource plan named PQ_STMT_PLAN.
begin
dbms_resource_manager.create_pending_area();
end;
/
--------------Create Resource Manager Plan -----------------------------
begin
dbms_resource_manager.create_plan(
plan => 'PQ_STMT_PLAN',
comment => 'Resource plan for PQ statement Queuing 12c');
end;
/
The following step creates four Resource Manager consumer groups. These consumer groups will be associated
with a plan directive in the next step.
-------------- Create Resource Manager Consumer Groups -------------------
begin
dbms_resource_manager.create_consumer_group(
consumer_group => 'PX_CRITICAL',
comment => 'Resource consumer group/method for online users / report sessions');
dbms_resource_manager.create_consumer_group(
consumer_group => 'PX_BATCH_HIGH',
comment => 'Resource consumer group/method for batch high users');
dbms_resource_manager.create_consumer_group(
consumer_group => 'PX_BATCH_MED',
comment => 'Resource consumer group/method for batch med users');
dbms_resource_manager.create_consumer_group(
consumer_group => 'PX_BATCH_LOW',
comment => 'Resource consumer group/method for batch low users');
end;
/
