For tables that have DEGREE DEFAULT, the degree of parallelism (DOP) is calculated by Oracle using number of CPUs and number of threads per CPU amongst other things. As I understand it, when costing execution plans Oracle will assume the default DOP for tables with DEGREE DEFAULT.

At execution time, however, if parallel_max_servers is less than the default DOP then the number of parallel servers will be restricted and - I presume - the final execution may not go as intended by the costed execution plan and a less parallel execution plan may be preferable.

Is this correct?

  • parallel_max_servers is higher than the highest possible DOP by default - have you turned it down? Oct 25, 2011 at 20:11
  • Hi Jack - this is on a shared server managed by others. I presume they're looking to prevent a huge number of threads from kicking off. That's a great link though - hadn't come across that one before. Do you think my logic is correct? It assumes that parallel_max_servers is ignored when costing an execution plan. Oct 26, 2011 at 16:07
  • I think your logic is correct in that if the parallelism is limited to below what the CBO expected when it complied the plan then perhaps a different plan would have been better. Oct 26, 2011 at 17:59

1 Answer 1


It would be a very strange thing if parallel_max_servers is set to a value below cpu_count * parallel_threads_per_cpu. I would call that a mistake by the DBA even. Let me bring to your attention a new feature of 11g (you should always mention your version, by the way), called Automatic DOP, that I have introduced in a posting here: http://uhesse.wordpress.com/2009/11/24/automatic-dop-in-11gr2/

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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