How to prevent CPU's overloading while using Parallel Query
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: How to prevent CPU's overloading while using Parallel Query

  1. #1
    Join Date
    Feb 2008
    Posts
    31

    How to prevent CPU's overloading while using Parallel Query

    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
    Last edited by amasny; 11-07-2008 at 08:28 AM.

  2. #2
    Join Date
    Jul 2002
    Location
    Lake Worth, FL
    Posts
    1,480

    Cool Dop?

    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.
    .
    "The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb

  3. #3
    Join Date
    Sep 2000
    Location
    Sao Paulo,SP,Brazil, Earth, Milky Way
    Posts
    350
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width