Beyond Data Access and Join Optimization - Troubleshooting Oracle Performance - page 605

In a RAC environment every database instance can have its own memory settings. The only exception is the
parallel_execution_message_size initialization parameter. In fact, database instances with different values for this
initialization parameter can't communicate and, therefore, raise an error when a SQL statement involving several
database instances is executed. The following is an example of such an error:

SQL> SELECT * FROM gv$instance;
SELECT * FROM gv$instance
*
ERROR at line 1:
ORA-12850: Could not allocate slaves on all specified instances: 2 needed, 1 allocated
ORA-12801: error signaled in parallel query server P001, instance 32766

Degree of Parallelism
The number of slave processes used for intra-operation parallelism is called degree of parallelism (DOP). Because
the degree of parallelism defines the number of slave processes for intra-operation parallelism, when inter-operation
parallelism is used, the number of slave processes used to execute a SQL statement is higher than the degree of
parallelism. In any case, note that a single data flow operation can't use more slave processes than two times the
degree of parallelism. For example, Figure 15-7 shows a case where the number of slave processes is twice the degree
of parallelism.
When a SQL statement (or part of it) is processed in parallel, the database engine has to select the degree of
parallelism that is used for that purpose. Even though there are several initialization parameters and other factors that
determine the actual degree of parallelism, there are really just two main modes in which to setup a database instance
to control the degree of parallelism:

• Manual degree of parallelism : In this mode, you can control the degree of parallelism either at
the session, object, or SQL statement level.

• Automatic degree of parallelism : In this mode, the database engine automatically selects the
optimal degree of parallelism for every SQL statement.

Manual degree of parallelism is the only mode available through version 11.1.

■ I advise you to use automatic degree of parallelism only from version 11.2.0.3 onward. The reason is that
in prior versions, several bugs make it difficult to successfully implement. refer to the oracle Support note entitled Init.
ora Parameter “PARALLEL_DEGREE_POLICY” Reference Note (1216277.1) for additional information. also note that some
changes in the functionality of automatic degree of parallelism were introduced in version 11.2.0.2. I don't think it makes
sense to use that approach prior to version 11.2.0.3, so the functionality of version 11.2.0.1 isn't covered in this topic.

Caution

From version 11.2 onward, the parallel_degree_policy initialization parameter is used not only to choose
between manual and automatic degree of parallelism, but also to enable other features related to parallel processing.
It accepts to one of the following values:

manual : Manual degree of parallelism is enabled. This is the default value. •

limited : Automatic degree of parallelism is enabled only for SQL statements that reference
objects whose PARALLEL is set to DEFAULT (see below for detail). For the others, manual degree
of parallelism is used.

•