PARALLEL_MIN_TIME_THRESHOLD parameter when AUTO or MANUAL mode

oracleoracle-12cparallelismperformanceperformance-tuning

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:

PARALLEL_MIN_TIME_THRESHOLD is the second initialization parameter that controls automatic DOP. It specifies the minimum execution time a statement should have before the statement is considered for automatic DOP. By default, this is 10 seconds. The optimizer first calculates a serial execution plan for the SQL statement; if the estimated execution elapsed time is greater than PARALLEL_MIN_TIME_THRESHOLD (10 seconds), the statement becomes a candidate for automatic DOP.

Another good resource is Using Default Parameter Settings, emphasis mine:

PARALLEL_MIN_TIME_THRESHOLD: The execution time, as estimated by the optimizer, above which a statement is considered for automatic parallel query and automatic derivation of DOP.

To answer your questions directly:

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?

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.

Also, is the estimated execution or the actual execute time used in the calculations around the threshold limit?

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.

Related Question