Database Reference
In-Depth Information
begin
dbms_resource_manager.submit_pending_area();
end;
/
In a nutshell, version 12c enhances the strict FIFO policy employed in earlier versions for PX statement
execution. A complex scheme such as the one discussed in this section can be used to prioritize the workload so that
higher-priority sessions do not have to wait for lower-priority parallel statement execution.
This enhancement is useful in mixed-workload environments such as highly critical short-running parallel
executions and low-priority long-running parallel executions mixed in the same database. If critical SQL statement
must wait for low-priority parallel statement execution, then critical parallel execution may be delayed. Worse,
low-priority execution can be a long-running statement with a higher number of PX servers, and so the wait time for
critical statements can be longer. By enabling prioritization within the workload, you can reduce the wait time for
critical SQL execution in parallel statement queues.
Critical Parallel Statement Queues (12c)
In the preceding section, you learned how to prioritize parallel statement execution among consumer groups.
You may recall that the Parallel Statement Queuing feature is enabled only after the active PX server count exceeds
the parallel_servers_target parameter. In some cases, you might prefer for ultra-critical jobs to acquire PX servers
completely bypassing the PX statement queue. For example, an important index must be rebuilt quickly: waiting in
the parallel statement queue is not acceptable, and you prefer to bypass it completely. As the parallel_servers_target
parameter is configured to be less than the parallel_max_servers parameter, it may be beneficial to bypass the queue
for ultra-critical workloads.
Version 12c introduces the attribute
parallel_stmt_critical
for a resource consumer group. If you set this
attribute to BYPASS_QUEUE, then sessions attached to this resource consumer group will bypass the PX statement
queue and start execution immediately. As the parallel_max_servers parameter is a hard threshold, it is possible for
this critical statement to execute with fewer PX servers than requested.
begin
dbms_resource_manager.create_pending_area();
end;
/
begin
dbms_resource_manager.create_consumer_group(
consumer_group => 'PX_ULTRA_CRIT',
comment => 'Resource consumer group/method for critical users');
dbms_resource_manager.create_plan_directive(
plan => 'PQ_STMT_PLAN',
group_or_subplan => 'PX_ULTRA_CRIT',
comment => 'Very Critical stuff',
parallel_stmt_critical => 'BYPASS_QUEUE'
);
end;
/
begin
dbms_resource_manager.validate_pending_area();
end;
/
begin
dbms_resource_manager.submit_pending_area();
end;
/
