If you use the Database Resource Manager to manage parallel server resources, and a consumer group uses up all its assigned resources, Oracle might be forced to downgrade the parallelism of the parallel statements issued by users assigned to that consumer group. You configure the allocation of parallel server resources for a consumer group with the PARALLEL_TARGET_PERCENTAGE directive. This directive specifies the maximum percentage of the parallel server pool that a particular consumer group can use. In 11.2.0.2, Oracle introduced parallel statement queuing, which offers the following features:
- When no more parallel servers are available, the parallel statement is queued. The parallel statement is dequeued and processed as parallel servers are freed up.
- A resource plan can be used to control the order of the parallel statement queue. When parallel servers are freed up, the resource plan is used to select a consumer group. The parallel query at the head of its queue is run.
- Parallel servers can be reserved for critical consumer groups.
This feature adds stability to the execution of SQL statements in parallel, but can introduce an additional wait time for parallel statements if the database server is running at full capacity. For a parallel statement to be queued, the following conditions must be met:
- The PARALLEL_DEGREE_POLICY initialization parameter is set to AUTO.
- The number of active parallel servers across all consumer groups exceeds the value of the PARALLEL_SERVERS_TARGET initialization parameter.
- The sum of the number of active parallel servers for the consumer group and the degree of parallelism of the parallel statement exceeds the target number of active parallel servers. In other words, (V$RSRC_CONSUMER_GROUP.CURRENT_PQ_SERVERS_ACTIVE + DOP of statement) > (PARALLEL_TARGET_PERCENTAGE/100 * PARALLEL_SERVERS_TARGET).
You can also use the NO_STATEMENT_QUEUING and STATEMENT_QUEUING hints in SQL statements to manage parallel statement queuing.
Leave a Reply