0

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?

Improve this question
2
  • Thank you for your question. Before you ask your next question, please consider reading the documentation more carefully and doing a bit more research on your own. The link that you had in the question answers the question that you asked here.
    – Joe Obbish
    Commented Jan 14, 2017 at 17:52
  • @JoeObbish Thanks for your answer and thanks for your reminder. I read some other untrusted material that the execution time is actual execution time not estimated execution time. I am confused with the point, so I ask it here help me confirm my learning. Thanks!!
    – shawn
    Commented Jan 14, 2017 at 17:58

1 Answer 1

Reset to default
1

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.

Improve this answer
4
  • If the hint is /*+ parallel(auto) */, it will check parallel_min_time_threshold. It that correct?
    – shawn
    Commented Jan 15, 2017 at 2:19
  • Let's take star transformation hint as an example. No one can guarantee that the transformation will take place even if you enforce to set an STAR_TRANSFORMATION hint.
    – shawn
    Commented Jan 15, 2017 at 8:09
  • If parallel(auto) means that Oracle will use automatic DOP then I would expect PARALLEL_MIN_TIME_THRESHOLD to have an effect. I do not understand the point you are making with the second comment.
    – Joe Obbish
    Commented Jan 15, 2017 at 18:02
  • Hints do not always take effect and optimizer will consider the performance under the case that statements enforce define the hint.
    – shawn
    Commented Jan 16, 2017 at 0:37

Not the answer you're looking for? Browse other questions tagged or ask your own question.