Skip Headers
Oracle® Database VLDB and Partitioning Guide
11g Release 2 (11.2)

Part Number E16541-05
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
View PDF

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:

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

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 ACTIVE_INSTANCES

Controls the maximum DOP a statement can have when AUTO 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

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

CPU_COUNT x PARALLEL_THREADS_PER_CPU x (2 if PGA_AGGREGATE_TARGET > 0; otherwise 1) x 5

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.

The value of PGA_AGGREGATE_TARGET can be derived when MEMORY_TARGET is set. See Oracle Database Performance Tuning Guide for descriptions of how to use PGA_AGGREGATE_TARGET in different scenarios.

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 serial if no parallel server processes are available.

PARALLEL_MIN_TIME_THRESHOLD

10 seconds

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

4 X CPU_COUNT X PARALLEL_THREADS_PER_CPU X ACTIVE_INSTANCE

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 slaves.

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.