When the parameter PARALLEL_DEGREE_POLICY is set to AUTO, Oracle Database queues SQL statements that require parallel execution if the necessary number of parallel execution server processes are not available. After the necessary resources become available, the SQL statement is dequeued and allowed to execute. The default dequeue order is a simple first in, first out queue based on the time a statement was issued.
The following is a summary of parallel statement processing.
A SQL statements is issued.
The statement is parsed and the DOP is automatically determined.
Available parallel resources are checked.
If there are sufficient parallel execution servers available and there are no statements ahead in the queue waiting for the resources, the SQL statement is executed.
If there are not sufficient parallel execution servers available, the SQL statement is queued based on specified conditions and dequeued from the front of the queue when specified conditions are met.
Parallel statements are queued if running the statements would increase the number of active parallel servers above the value of the PARALLEL_SERVERS_TARGET initialization parameter. For example, if PARALLEL_SERVERS_TARGET is set to 64, the number of current active servers is 60, and a new parallel statement needs 16 parallel servers, it would be queued because 16 added to 60 is greater than 64, the value of PARALLEL_SERVERS_TARGET.
This value is not the maximum number of parallel server processes allowed on the system, but the number available to run parallel statements before parallel statement queuing is used. It is set lower than the maximum number of parallel server processes allowed on the system (PARALLEL_MAX_SERVERS) to ensure each parallel statement gets all of the parallel server resources required and to prevent overloading the system with parallel server processes. Note all serial (nonparallel) statements execute immediately even if parallel statement queuing has been activated.
If a statement has been queued, it is identified by the resmgr:pq queued wait event.
This section discusses the following topics:
About Managing Parallel Statement Queuing with Oracle Database Resource Manager
Grouping Parallel Statements with BEGIN_SQL_BLOCK END_SQL_BLOCK
For information about views for monitoring and analyzing parallel statement queuing, refer to "V$RSRC_SESSION_INFO" and "V$RSRCMGRMETRIC".
Oracle Database Reference for more information about the PARALLEL_SERVERS_TARGET initialization parameter