From How Parallel Execution Works:
The following is a summary of parallel statement processing when parallel degree policy is set to automatic.
A SQL statement is issued.
The statement is parsed and the optimizer determines the execution plan.
The threshold limit specified by the PARALLEL_MIN_TIME_THRESHOLD initialization parameter is checked.
If the execution time is less than the threshold limit, the SQL statement is run serially.
If the execution time is greater than the threshold limit, the statement is run in parallel based on the DOP that the optimizer calculates.
Suppose the parallel_degree_policy is MANUAL
and the SQL statement contains a parallel hint or a defined parallel DDL value. Will the Oracle optimizer still check PARALLEL_MIN_TIME_THRESHOLD
?
Also, is the estimated execution or the actual execute time used in the calculations around the threshold limit?
Best Answer
The following quote is from your link, emphasis mine:
Another good resource is Using Default Parameter Settings, emphasis mine:
To answer your questions directly:
No,
PARALLEL_MIN_TIME_THRESHOLD
will not be used. If you specify the degree of parallelism why would the query optimizer calculate a cost for the serial plan? What would it do with that information? You've already specified the DOP. This only makes sense in the context of automatic DOP.This has to be the estimated time. Does it make sense for Oracle to possibly execute most of the query in serial and then switch to a parallel plan? As far as I understand it that will not happen, DOP is chosen at the beginning of execution for a data flow operation.