How to prevent CPU's overloading while using Parallel Query
I have short question :
I would like to implement PQ on select using hint : /*+ parallel(alias,DOP) */
How to prevent cpu’s from overloading using DOP parameter ?
Use more or less DOP ?
I would like to use DOP for this task because it is simplest way in my opinion.
I know about resource manager but it requires some time and testing to implement.
We have two separate databases ran on computer with 8 CPU’s, so using parameter : parallel_adaptive_multi_user won’t be good solution, I think.
Regarding using resource manager : How to implement directive for some group : NOT to using first CPU at all and use other CPU's left in machine ?
Last edited by amasny; 11-07-2008 at 08:28 AM.
Many official metalink notes (like 280939.1 , 199272.1 , 399086.1 , etc) use DOP for Degree Of Parallelism , so itīs an "official" acronym, I guess...
Regarding the question : sure, parallel_adaptive_multi_user would be a VERY nice thing, ** IF ** the questioner had obeyed the rule, ONE database in ONE server, having two, no, nope : afaik the algorithm for parallel_adaptive_multi_user check number of session inside ONE database, it would not "know" nothing about the other db.... SO the answer is : manually, inside your application, CHECK the number of current parallel sessions and set DOP manually , like this :
IF number of active parallel slaves < your limit THEN
SELECT /*+ PARALLEL(x) ...
Probably will exist just a few situations (ie, system in idle, semi-idle, normal and busy states), SO will be possible to check for this with IFs, no need for dynamic SQL, I think.
Click Here to Expand Forum to Full Width