How to prevent CPU's overloading while using Parallel Query

[amasny]

Hi,

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 ?


Best Regards
Arek Masny

[LKBrwn_DBA]

Never heard of DOP parameter unless it's some "acronym" you invented (it's not in any of the 10g or 11g manuals).

Try settiing the value to 2 times the number of processors you wish to use, for example:

/*+ parallel(alias,14) */ to use only 7 of the 8 processors.
.

[JChiappa]

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) ...
ELSE...

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.

Regards,

Chiappa