Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2003
    Posts
    3

    Unanswered: Number of partitions versus number of worker processes

    Is it necessary to have as many or more worker processes as you have partitions on a table you are querying? I currently have a table with 12 partitions but the maximum number of worker processes is 3. Would there be any drawbacks to turning up the number of worker processes to 12?

  2. #2
    Join Date
    Sep 2002
    Location
    Hong Kong
    Posts
    159

    Re: Number of partitions versus number of worker processes


    Would there be any drawbacks to turning up the number of worker processes to 12?
    How many engines are you running with? If you have 12 engines and can dedicate them all to a single process, no problem, however if you have 1 engine, you'll probably make things worse.

    Richard.

  3. #3
    Join Date
    Jan 2003
    Posts
    3

    Re: Number of partitions versus number of worker processes

    We have 9 engines but are definately not the only process running on this server. I do not understand how the distribution should work. Our tables have either 4 or 12 partitions but we have 9 engines, 8 devices and a maximum of 3 worker processes. Something does not seem right to me but I do not know enough to convince our DBAs.

  4. #4
    Join Date
    Sep 2002
    Location
    Hong Kong
    Posts
    159

    Re: Number of partitions versus number of worker processes

    Originally posted by control_freak
    We have 9 engines but are definately not the only process running on this server. I do not understand how the distribution should work. Our tables have either 4 or 12 partitions but we have 9 engines, 8 devices and a maximum of 3 worker processes. Something does not seem right to me but I do not know enough to convince our DBAs.
    Worker processes have a multiplying effect, so it's easy to mutiply up the number of worker processes required to process a query.

    Consider the following. Joining a table with 4 partitions to a table with 12 partitions, requires 48 worker processes, since there aren't that many available then it's unlikely the optimiser will choose a parition based scan.

    Having a low number of available worker processes means that hash based scans can be used, without utilising all the CPUs in the system and starving other tasks.

    The optimal solution is to have only one partition on a device and each device with it's own controller and bus. Unfortunately the real world doesn't always lend itself to optimal solutions.

Posting Permissions

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