APPLIES TO:
Oracle Database - Enterprise Edition - Version 9.2.0.1 and laterInformation 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.)
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).
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
-- 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.)
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.
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/21754115/viewspace-1379049/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/21754115/viewspace-1379049/