Initializing and Tuning Parameters for Parallel Execution

Oracle Database computes defaults for the parallel execution parameters based on the value at database startup of CPU_COUNT and PARALLEL_THREADS_PER_CPU. The parameters can also be manually tuned, increasing or decreasing their values to suit specific system configurations or performance goals. For example:

On systems where parallel execution is never used, PARALLEL_MAX_SERVERS can be set to zero.
On large systems with abundant SGA memory, PARALLEL_EXECUTION_MESSAGE_SIZE can be increased to improve throughput.

You can also manually tune parallel execution parameters. Parallel execution is enabled by default.

Initializing and tuning parallel execution involves the following steps:

Using Default Parameter Settings
Forcing Parallel Execution for a Session

Using Default Parameter Settings

By default, Oracle Database automatically sets parallel execution parameters, as shown in Table 8-3.

Table 8-3 Parameters and Their Defaults

Parameter	Default	Comments
`PARALLEL_ADAPTIVE_MULTI_USER`	`TRUE`	Causes parallel execution SQL to throttle degree of parallelism (DOP) requests to prevent system overload.
`PARALLEL_DEGREE_LIMIT`	`CPU_COUNT` X `PARALLEL_THREADS_PER_CPU` X number of instances available	Controls the maximum DOP a statement can have when automatic DOP is in use.
`PARALLEL_DEGREE_POLICY`	`MANUAL`	Controls whether auto DOP, parallel statement queuing and in-memory parallel execution are used. By default, all of these features are disabled.
`PARALLEL_EXECUTION_MESSAGE_SIZE`	16 KB	Specifies the size of the buffers used by the parallel execution servers to communicate among themselves and with the query coordinator. These buffers are allocated out of the shared pool.
`PARALLEL_FORCE_LOCAL`	`FALSE`	Restricts parallel execution to the current Oracle RAC instance.
`PARALLEL_MAX_SERVERS`	See "PARALLEL_MAX_SERVERS".	Specifies the maximum number of parallel execution processes and parallel recovery processes for an instance. As demand increases, Oracle Database increases the number of processes from the number created at instance startup up to this value. If you set this parameter too low, some queries may not have a parallel execution process available to them during query processing. If you set it too high, memory resource shortages may occur during peak periods, which can degrade performance.
`PARALLEL_MIN_SERVERS`	0	Specifies the number of parallel execution processes to be started and reserved for parallel operations, when Oracle Database is started up. Increasing this setting can help balance the startup cost of a parallel statement, but requires greater memory usage as these parallel execution processes are not removed until the database is shut down.
`PARALLEL_MIN_PERCENT`	0	Specifies the minimum percentage of requested parallel execution processes required for parallel execution. With the default value of 0, a parallel statement executes serially if no parallel server processes are available.
`PARALLEL_MIN_TIME_THRESHOLD`	10 seconds	Specifies the execution time, as estimated by the optimizer, above which a statement is considered for automatic parallel query and automatic derivation of DOP.
`PARALLEL_SERVERS_TARGET`	See "PARALLEL_SERVERS_TARGET".	Specifies the number of parallel execution server processes available to run queries before parallel statement queuing is used. Note that parallel statement queuing is only active if `PARALLEL_DEGREE_POLICY` is set to `AUTO`.
`PARALLEL_THREADS_PER_CPU`	2	Describes the number of parallel execution processes or threads that a CPU can handle during parallel execution.

Note that you can set some parameters in such a way that Oracle Database is constrained. For example, if you set PROCESSES to 20, you are not be able to get 25 child processes.

Forcing Parallel Execution for a Session

If you are sure you want to execute in parallel and want to avoid setting the DOP for a table or modifying the queries involved, you can force parallelism with the following statement:

ALTER SESSION FORCE PARALLEL QUERY;

All subsequent queries are executed in parallel provided no restrictions are violated. You can also force DML and DDL statements. This clause overrides any parallel clause specified in subsequent statements in the session, but is overridden by a parallel hint.

In typical OLTP environments, for example, the tables are not set parallel, but nightly batch scripts may want to collect data from these tables in parallel. By setting the DOP in the session, the user avoids altering each table in parallel and then altering it back to serial when finished.