Configuration parameters for controlling parallelism

The configuration parameters that give you control over the degree of parallelism server-wide are shown in Table 24-1.

Table 24-1: Configuration parameters for parallel execution

Parameter

Explanation

Comment

number of worker processes

The maximum number of worker processes available for all parallel queries. Each worker process requires approximately as much memory as a user connection.

Restart of server required

max parallel degree

The number of worker processes that can be used by a single query. It must be equal to or less than number of worker processes and equal to or greater than max scan parallel degree.

Dynamic, no restart required

max scan parallel degree

The maximum number of worker processes that can be used for a hash scan. It must be equal to or less than number of worker processes and max parallel degree.

Dynamic, no restart required

Configuring number of worker processes affects the size of the data and procedure cache, so you may want to change the value of total memory also.

For more information see the System Administration Guide.

When you change max parallel degree or max scan parallel degree, all query plans in cache are invalidated, so the next execution of any stored procedure or trigger recompiles the plan and uses the new values.