Parallel Query in RAC - Expert Oracle RAC 12c - page 354

PX execution initiated from orcl1 can allocate PX servers in all instances, since all three instances are members of the
scm instance group.
orcl1.parallel_instance_group= 'scm'
If we set the parallel_instance_group parameter to po in orcl1 instance, then PX executions initiated from the
orcl1 instance can allocate PX servers from both orcl1 and orcl2 instances.
orcl1.parallel_instance_group= 'po'
If the parameter parallel_instance_group is set to finance in orcl3, then the PX execution can allocate PX servers
only from the orcl3 instance, as only the orcl3 instance is a member of the finance instance group.
Orcl3.parallel_instance_group= 'finance'
If you set the parameter parallel_instance_group to finance in the orcl2 instance, then the PX execution initiated
from the orcl2 instance will spawn PX servers in the orcl3 instance, as orcl3 is the sole member of the finance
instance group.
Orcl2.parallel_instance_group= 'finance'
If the specified parallel_instance_group parameter value has no members, then the parallel execution will be
disabled completely. For example, a parameter value of ar will disable parallel exectuion, since there are no instances
in the ar instance group.
*.parallel_instance_group= 'ar'
You can alter the value of the parallel_instance_group parameter dynamically at either the session level or the
system level. For example, the following alter session statement enables PX server allocation in orcl1 and orcl2
instances in the session. You can override instance- or database-level setup with session-level changes.
Alter session set parallel_instance_group= 'po';
The parameter instance_group cannot be altered dynamically (since release 11g), and instance restarts would be
required to alter the instance_group parameter. There is no explicit method to create an instance group.
As of version 11g, the optimizer considers the parallelism setup before choosing a PX plan. If parallelism is
disabled, in the session or globally, the optimizer will not choose a PX plan. Prior to version 11g, the optimizer could
choose a PX plan even if the parallelism was disabled. At run time, the execution engine would execute the chosen
PX plan serially, leading to worse performance. From Database version 11.2.0.2 onward, the cost-based optimizer is
also aware of the parallelism setup. If the parallelism is disabled in the session, through either session-level parameter
setup or global-level parameter setup, then the optimizer does not choose a parallel plan at all. For example,
I modified the parallel_instance_group to dummy and executed the following statement. Cost-based optimizer 10053
event trace indicates that optimizer used a parallel degree of 1, even though the hint specifies a DOP of 8. Also notice
that the cost of parallel plan (resp) is same as the cost of serial plan (Resc), indicating that the optimizer will not
choose a PX plan.
alter session set events '10053 trace name context forever, level 1';
REM parallel_instance_group value is non-existent.
alter session set parallel_instance_group='dummy';