I have a client who reports that in general, the response time of slow transactions is better when they turn max_query_tasks off, which is the opposite of what we expected to see. They're using SA 11.0.1.2584 |
Is the setting of "max-query-tasks" the default of 0? (ie unlimited?) And I assume the setting of optimization-goal is 'all-rows'? and the setting of optimization-level is 9? There are a number of factors that interplay here that can impact actual performance. Without getting into gory detail, the optimizer makes a cost-based decision between parallel and non-parallel plans. Two important things occur when the optimizer considers parallel plans:
While our default for max-query-tasks is 0 (unlimited parallelization based on the optimizer's choices) I would be tempted as an administrator to tradeoff higher degrees of parallelism with ensuring that cores/workers are available to handle new connections or other long-running ad-hoc requests that can affect the system's response to peak workloads. Glenn, I have a question to the situation you point to in number 2: With v12's automatic tuning of the MPL feature, would a plan that is chosen for more workers than are available when the execution is about to start
(Assumed the increased number would still be below the maximum level)?
(27 May '11, 03:49)
Volker Barth
Replies hidden
1
The correct answer is
(27 May '11, 09:43)
Glenn Paulley
Thanks for the clarification - and, yes, I should've known that smart heuristics don't "increase immediately" but "may decide ... under certain conditions":)
(27 May '11, 11:18)
Volker Barth
|
Have you compared the query plans for both options? I would seek the cause for your observation there.
I assume "they turn max_query_tasks off" means they set it to 1, right?
From the docs: "Setting the max_query_tasks option to 1 disables intra-query parallelism."