Database Reference
In-Depth Information
The next step creates the priorities among parallel statement queues of the resource consumer groups. Directive
mgmt_p1
decides the priority of a specific queue in level 1. In the following example, SYS_GROUP has a 100% priority
at level 1. A parallel statement with SYS_GROUP as the consumer group will be chosen next to be executed. If there is
no statement belonging to SYS_GROUP that is waiting in the parallel statement queue, then a parallel query from the
next-lowest-level PX statement queues will be considered for execution.
Directive mgmt_p2 decides the priority of the parallel statement queue at level 2, and the value of mgmt_p2 is set
to 50 for the consumer group PX_CRITICAL. So, there is a 50% probability that the next query will be scheduled for the
sessions with a resource consumer group associated with the PX_CRITICAL group. Similarly, PX_BATCH_HIGH has a
30% probability that a PX session for the PX_BATCH_HIGH consumer group will be chosen to be executed next. Also,
PX_BATCH_LOW and PX_BATCH_MED have a probability of 10%.
The priority of OTHER_GROUPS is set to 100 at level 3 by setting the mgmt_p3 attribute. A statement belonging to
OTHER_GROUPS will be executed only if there is no other statement waiting for higher-level queues.
------------- Create Resource Manager Plan Directives --------------------
begin
dbms_resource_manager.create_plan_directive(
plan => 'PQ_STMT_PLAN',
group_or_subplan => 'SYS_GROUP',
comment => 'SYS sessions at level 1',
mgmt_p1 => 100 );
dbms_resource_manager.create_plan_directive(
plan => 'PQ_STMT_PLAN',
group_or_subplan => 'PX_CRITICAL',
comment => 'Online day users sessions at level 1',
mgmt_p2 => 50);
dbms_resource_manager.create_plan_directive(
plan => 'PQ_STMT_PLAN',
group_or_subplan => 'PX_BATCH_HIGH',
comment => 'Batch users level 2 -HIGH- Fast running ',
mgmt_p2 => 30);
dbms_resource_manager.create_plan_directive(
plan => 'PQ_STMT_PLAN',
group_or_subplan => 'PX_BATCH_MED',
comment => 'Batch users sessions at level 2 - MED - Medium running',
mgmt_p2 => 10);
dbms_resource_manager.create_plan_directive(
plan => 'PQ_STMT_PLAN',
group_or_subplan => 'PX_BATCH_LOW',
comment => 'Batch users sessions at level 2 - LOW - long running',
mgmt_p2 => 10);
dbms_resource_manager.create_plan_directive(
plan => 'PQ_STMT_PLAN',
group_or_subplan => 'OTHER_GROUPS',
comment => 'Batch users sessions for Other groups',
mgmt_p3 => 100);
end;
/
begin
dbms_resource_manager.validate_pending_area();
end;
/
