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?
Best Answer
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/