APPLIES TO:

Oracle Database - Enterprise Edition - Version 9.2.0.1 and later
Information in this document applies to any platform.

GOAL

You want to know why the parallel execution of your SQL statement is using a high value for degree of parallelism (DOP).

SOLUTION

Factors that may cause a high calculated value of DOP:

1. Object degree is set to DEFAULT

The default Degree of Parallelism (DOP) is dependent on CPU_COUNT, which can be the virtual number and not the physical number. This can lead to a high default DOP when there are a lot of virtual CPUs, especially in a RAC database, where the number of instances is also in the calculation. (Note: The specified object DOP is ignored when (1) parallel_degree_policy = AUTO, and (2) when and the object DOP is DEFAULT and parallel_degree_policy = LIMITED; in these cases, DOP is automatically determined based on various factors and IO calibration statistics. If IO calibration statistics do not exist, the manual behavior is used.)

-- find tables and indexes with a degree of DEFAULT

col owner for a15

col table_name for a35

col index_name for a35

select owner, table_name, degree from dba_tables where degree like '%DEFAULT%' order by owner, table_name;

select owner, index_name, degree from dba_indexes where degree like  '%DEFAULT%' order by owner, index_name;

Solution:
Either (a) alter the objects so they no longer have a DEFAULT degree or (b) set parallel_degree_limit to a specific integer (we recommend a value of 32 or lower; keep in mind that normally 2 slave sets, each of size DOP, are used for a query; sometimes more than 2 slave sets can be needed).

-- change the degree of parallelism for an object (Note: you should always have the specified DOP on an index match to the DOP of its table.)

alter table  parallel 4; -- replace  with the correct value

alter index  parallel 4; -- replace  with the correct value

-- or --

-- set parallel_degree_limit to a specific integer

alter system set parallel_degree_limit = 16;

2. Parallel hint doesn't specify the DOP to use or parallel hint is invalid

-- Note: in 11.2+ if IO calibration statistics exist, even if parallel_degree_policy = MANUAL, if the parallel hint is used wihtout a degree specified, AutoDOP will be used.

-- this statement-level hint will cause DEFAULT parallelism to be calculated on all objects in the query (11.2+)

select /*+ parallel */ * from dba_objects;

-- this hint will cause DEFAULT parallelism to be calculated on the hinted object(s)in the query

select /*+ parallel(o) */ * from dba_objects o;

--  this is an invalid hint ,so DEFAULT parallelism will be used

select /*+ parallel 8 */ * from dba_objects;

Solution:
Change the hinted parallelism to a specific integer and fix invalid hints. (A degree explicitly set in a hint will override AutoDOP and DEFAULT calculations.)

-- specify the desired DOP for all objects in the query (statement-level hints are available from 11g onward)

select /*+ parallel(4) */ * from dba_objects;

-- specify the desired DOP for each object in the query 

select /+* parallel(o,4) */ * from dba_objects o;

-- fix the invalid parallel hint

select /*+ parallel (8) */ * from dba_objects;

3. Parameter parallel_degree_policy is set to AUTO or LIMITED (11.2 or higher only)

As long as the requirements are met for AutoDOP (i.e. IO calibration statistics exist), AutoDOP will calculate the optimal DOP for you (regardless of degree on objects); sometimes this can be high.

Solution:
Limit the calculated AutoDOP by virtue of setting parallel_degree_limit to a specific integer. Note that when parallel_degree_policy = LIMITED, this limit will apply only to objects that have a DOP of DEFAULT; the limit will be ignored if the object DOP is an integer and parallel_degree_policy = LIMITED.

alter system set parallel_degree_limit = 32;

Why Is My Query Using a High Value for Degree of Parallelism