Parallel Execution – Precedence of Hints and other Factors
By Jean-Pierre Dijcks-Oracle on Jun 13, 2011
The following table is a reflection of the precedence of hints, things like alter session enable parallel DML when using Auto DOP. It is also important to understand how the DML and query parts work together and how they influence each other.
All of the below is based on a simple statement:
insert into t3 as select * from t1, t2 where t1.c1 = t2.c1;
Some explanatory words based on the lines in the picture above:
Line 1 => The cleanest way to run PX statements, where Auto DOP gets to do its work and we will use the computed DOP of the statement
Line 4 => Because a FORCE parallel is used, we must ensure that the DOP > 1
Line 9 => The statement level hint over rides all other means and we run the statement with the DOP in the hint
A word on internal degree limit. This is NOT (repeat NOT) a parameter you can find, or set or find an underscore for. It is the built in limit to DOPs (set to default DOP or parallel_degree_limit = CPU). It is an internal boundary to ensure we do not blast through the upper bound of CPU power. Also note, for any non-compute degree, those boundaries and limits do not apply. That in itself is a reason to go look at and understand Auto DOP.