Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 2 down vote favorite

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?

share|improve this question
parallel_max_servers is higher than the highest possible DOP by default - have you turned it down? – Jack Douglas♦ Oct 25 '11 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. – Nick Pierpoint Oct 26 '11 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. – Jack Douglas♦ Oct 26 '11 at 17:59

1 Answer

up vote 1 down vote

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/

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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