8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux

Home » Articles » 11g » Here

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.

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:

Hope this helps. Regards Tim...

Back to the Top.