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

lovestanford發表於2014-12-24

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;

來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/21754115/viewspace-1379049/,如需轉載,請註明出處,否則將追究法律責任。

相關文章