Results 1 to 9 of 9
  1. #1
    Join Date
    Oct 2005
    Posts
    61

    Unanswered: Parallel Execution

    Hi,

    When we set
    parallel_automatic_tuning=true
    parallel_max_servers=96

    What does this imply? Does it imply that parallelism would be managed automatically or does it imply that parallelism would be managed automatically only if the table is created with some degree of parallelism?

    And when we hint (/* parallel (tablename number) */ in select and dml, then irrespective of all these settings it executes in parallel is it?

    Are there any other parameters to be set apart from the above?

    On what basis do we set the parallel_max_servers? Is it based on any ratio wrt the number of cpus? Same question with regard to degree of parallelism while creating a table and while specifying hints. A few articles say the dop must be twice the number of cpus. Not sure about that though.

    Thanks,
    Srinivas

  2. #2
    Join Date
    Jul 2003
    Posts
    2,296
    table must be set to parallel I believe
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  3. #3
    Join Date
    May 2006
    Posts
    132
    Once your pfile/spfile parameters are configured, parallel queries will be conducted based either on the table settings or the hint (if specified, this will override the table setting).

    To perform parallel DML, you must enable parallel dml in the session with:

    SQL> alter session enable parallel dml;


    If PARALLEL_AUTOMATIC_TUNING is set to TRUE, the default for PARALLEL_MAX_SERVERS is 10 * CPU's, but it would be best to set the value and test what works best for you.

  4. #4
    Join Date
    Oct 2005
    Posts
    61
    Thanks!

    So, I have to set the pfile/spfile parameters if I need parallelism, be it hints or table level, is that right?

    And on what basis do I have to set the degree of parallelism at table level or using hints? (like is there any specific ratio or number like 2*cpus etc?)

    Thanks,
    Srinivas

  5. #5
    Join Date
    May 2006
    Posts
    132
    You have to set the pfile/spfile parameters AND then configure the table and/or hint.

    Hard to give a definitive DOP, you'll probably want to do some testing to determine what DOP works best in your environment.

  6. #6
    Join Date
    Oct 2005
    Posts
    61
    Thanks very much!

  7. #7
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    Its probably worthwhile setting parallel_automatic_tuning to true and set parallel_threads_per_cpu to the max number of parallel threads one of your CPUs can support. This way if someone else does a parallel query you dont bring your prod database to a grinding halt as can often happen when there too many parallel processes.

    Alan

  8. #8
    Join Date
    Oct 2005
    Posts
    61
    Alan,

    Thanks very much for the good point. Do you know how to determine the max number of parallel threads that a cpu can support?

    Thanks,
    Srinivas

  9. #9
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    You could actually hog a processor especifying many threads. I believe you could especify as much threads as you want for a cpu; however, not that you would like to do that. Think about what will happen in the event you especify many threads for a cpu and they are a lot of concurrent users.

    BTW, ebrian, excellent explanation.

Posting Permissions

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