Parallel Query Tuning - Expert Oracle RAC Performance Diagnostics and Tuning - page 241

oracle 22599 1 23 19:36 ? 00:00:06 ora_p004_SSKYPRD_1
oracle 22601 1 24 19:36 ? 00:00:07 ora_p005_SSKYPRD_1
oracle 22603 1 25 19:36 ? 00:00:07 ora_p006_SSKYPRD_1
oracle 22605 1 26 19:36 ? 00:00:07 ora_p007_SSKYPRD_1
NOPARALLEL
NOPARALLEL overrides the default DOP on the statement or the PARALLEL specification at the object level and disables
parallel operation on the statement. For example
SELECT /*+ NOPARALLEL */ * FROM PRODUCT
PQ_DISTRIBUTE
This hint controls the distribution method for a specified join operation. The syntax of this hint is /*+ PQ_DISTRIBUTE
(tablespace name, distribution) */ where distribution is the distribution method (e.g., “PARTITION” is a
distribution method) to use between the producer and consumer slaves for the left and the right side of the join.
The hint applies to parallel INSERT... SELECT FROM... and parallel CREATE TABLE AS SELECT statements to
specify how rows should be distributed between the producer (query) and the consumer (load) slaves.
In complex query statements where there are several tables/objects involved, it may be required to specify a full
set of hints to ensure the optimal execution plan is obtained. If a full set of hints is not specified, then the optimizer
will have to determine the remaining access paths to be used and the corresponding join methods. This means the
partial hint definitions may not be used because the optimizer might have determined that the requested hint cannot
be used due to the join methods and access paths selected by the optimizer.
Initialization Parameters
Like any feature in Oracle, apart from the hints and methods to enable, request, and optimize the feature, there are
parameters that could set at the instance level. These parameters apply to all sessions that run on the instance.
PARALLEL_MIN_SERVERS
This parameter specifies the minimum number of parallel execution processes for the instance. The default value
for this parameter is 0. If a value greater than 0 is defined, then Oracle starts the parallel execution processes at
instance startup. The myth that this is a RAC-only parameter is wrong because it just defines the minimum parallel
servers started during instance startup. This parameter applies to both a RAC and non-RAC environment. In a RAC
environment, queries against the GV$ views occur in parallel, and setting this parameter to a higher number such as 2
or 4 would help in this operation.
PARALLEL_MAX_SERVERS
This parameter specifies the maximum number of parallel execution processes and parallel recovery processes
that can be started for an instance. The default value is set to (10 * CPU_COUNT * PARALLEL_THREADS_PER_CPU) .
By defining a max value, processes are only added on an as-needed basis to the maximum defined by this parameter.
Careful attention should be given to setting this value. If the value is set too high, this could cause degraded
performance, especially when sufficient resources are not available.