8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux
Auto DOP and Parallel Statement Queuing (PARALLEL_DEGREE_POLICY and PARALLEL_SERVERS_TARGET)
Oracle 11g Release 2 (11.2) introduced some big changes associated with the control of parallel execution. This article gives an overview of Auto DOP and Parallel Statement Queuing, which can be used to give a degree of control over the amount of parallel execution happening on the server at any one time.
Related articles.
The Problem
You have a system that uses parallelism heavily, either by setting it at the object level, or using hints in SQL.
-- Object setting. ALTER TABLE my_table PARALLEL 16; -- Object-level hint. SELECT PARALLEL(my_table 16)... FROM my_table... -- Statement-level hint. SELECT PARALLEL(16)... FROM my_table...
Your users start to request reports all at the same time and the system is brought to its knees under the weight of all the parallel execution slaves. What do you do?
Also, what do you do when your user base changes, or your hardware changes? Revisit all the decisions you made in the past?
Auto DOP
Automatic Degree of Parallelism, or Auto DOP, goes some way to solving these problems by allowing Oracle to determine the DOP of a statement, rather than object settings or hints.
The optimizer generates a serial plan and estimates the execution time. If this is below the threshold limit the statement is run serially. If it is above the threshold limit, the optimizer generates a parallel plan and determines the DOP based on the resource requirements.
Auto DOP is enabled by setting the PARALLEL_DEGREE_POLICY
parameter.
ALTER SYSTEM SET PARALLEL_DEGREE_POLICY = AUTO;
Allowable values are as follows.
- MANUAL : (DEFAULT) Disables auto DOP, statement queuing and in-memory parallel execution. Resembled pre-11.2 behaviour.
- LIMITED : Enables auto DOP for statements against objects with parallel settings. Statement queuing and in-memory parallel execution are disabled. Resembled pre-11.2 behaviour.
- AUTO : Enables auto DOP, statement queuing and in-memory parallel execution.
- ADAPTIVE : (12c) This is like AUTO with performance feedback to allow additional reoptimization of subsequent executions.
The PARALLEL_MIN_TIME_THRESHOLD
parameter represents the number of seconds the statement takes to run before auto DOP will consider it for parallel. It default to AUTO, which is 10 seconds.
ALTER SYSTEM SET PARALLEL_MIN_TIME_THRESHOLD = 20; ALTER SYSTEM SET PARALLEL_MIN_TIME_THRESHOLD = AUTO;
The PARALLEL_DEGREE_LIMIT
parameter allows to influence the DOP used by the optimizer, when it decides to use parallel execution. The default setting is CPU, which is calculated as follows.
Single Instance: DOP = PARALLEL_THREADS_PER_CPU x CPU_COUNT RAC : DOP = PARALLEL_THREADS_PER_CPU x SUM(CPU_COUNT)
So we can see the PARALLEL_DEGREE_LIMIT
would be calculated as 16 for the following single instance system.
SQL> SHOW PARAMETER PARALLEL_DEGREE_LIMIT NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ parallel_degree_limit string CPU SQL> SHOW PARAMETER PARALLEL_THREADS_PER_CPU NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ parallel_threads_per_cpu integer 2 SQL> SHOW PARAMETER CPU_COUNT NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ cpu_count integer 8 SQL>
Hints can still be used to influence parallelism at the statement level.
-- Force a statement to use parallel. SELECT /*+ PARALLEL */ ... -- Force a statement to use parallel with DOP 10. SELECT /*+ PARALLEL(10) */ ... -- Force serial execution. SELECT /*+ NO_PARALLEL */ ... -- Let Oracle decide on th DOP. SELECT /*+ PARALLEL(AUTO) */ ... -- Use pre-11.2 mechanism. SELECT /*+ PARALLEL(MANUAL) */ ...
Parallel Statement Queuing
Parallel statement queuing allows you to stop a system getting flooded with parallel server processes. Rather than running all parallel queries immediately, Oracle will check there are enough parallel resources available. If so, the parallel query runs. If not, the statement is queued until enough resources are available. If used correctly, queue time plus execution time should be less that execution time of all the statements happening at once.
Parallel queuing does not affect queries that are run serially, so this should be factored into the resource available for parallel execution.
The amount of parallel servers that can be used before parallel statement queuing is considered is defined using the PARALLEL_SERVERS_TARGET
parameter. The default value for this parameter is shown below.
PARALLEL_SERVERS_TARGET = PARALLEL_THREADS_PER_CPU * CPU_COUNT * concurrent_parallel_users * 2
The concurrent_parallel_users
value is derived from some other parameters.
IF MEMORY_TARGET or SGA_TARGET specified THEN concurrent_parallel_users = 4 ELSIF PGA_AGGREGATE_TARGET specified THEN concurrent_parallel_users = 2 ELSE concurrent_parallel_users = 1 END IF;
You can set this value as appropriate for your system.
ALTER SYSTEM SET PARALLEL_SERVERS_TARGET = 128;
The "resmgr:pq queued" wait event indicates a session was waiting while a statement was queued.
If you need a finer level of control over parallel statement queuing you can do this with resource manager (here).
For more information see:
- Setting the Degree of Parallelism
- PARALLEL Hint
- PARALLEL_DEGREE_POLICY
- PARALLEL_MIN_TIME_THRESHOLD
- Parallel Statement Queuing
- PARALLEL_SERVERS_TARGET
- About Managing Parallel Statement Queuing with Oracle Database Resource Manager
- Instance Caging to Manage CPU Usage in Oracle Database 11g Release 2
Hope this helps. Regards Tim...