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

PARALLEL_ADAPTIVE_MULTI_USER
This parameter enables the adaptive algorithm and helps improve performance in environments that use parallel
executions. The algorithm automatically reduces the requested DOP based on the system load at query startup time.
The effective DOP is based on the default DOP, or the degree from the table or hints, divided by a reduction factor.
Having PARALLEL_ADAPTIVE_MULTI_USER set to TRUE (default value) causes the DOP to be calculated as
PARALLEL_THREADS_PER_CPU * CPU_COUNT * ( a reduction factor ) . The purpose of this parameter is to allow
as many users as possible to concurrently run queries in parallel, taking into account the number of CPUs on the
machine. As more parallel queries are issued, the number of slaves allocated to each will be reduced, thus preventing
parallel queries from being forced to run serially or failing with the following error:
ORA-12827 insufficient parallel query slaves available
This parameter helps in the optimal utilization of resources; however, the response time of the operation maybe
inconsistent. Reduced DOP could affect parallel operations that are time critical, providing inconsistent response
times. Avoiding such inconsistent execution may require that a minimal DOP is guaranteed for the operation; this can
be done by using the parameter PARALLEL_MIN_PERCENT .
PARALLEL_MIN_PERCENT
This helps specify the minimum percentage of parallel server processes required to start the parallel operation. This
parameter defaults to 0, meaning that Oracle will always execute the statement, irrespective of the number of parallel
server processes available. Once this parameter is set and there are insufficient parallel query servers available, error
ORA-12827 is generated and the statement will not execute.
PARALLEL_THREADS_PER_CPU
This parameter specifies the default DOP for the instance and determines the parallel adaptive and load-balancing
algorithm. It describes the number of parallel execution threads that a CPU can handle during parallel execution.
It is used to calculate the default DOP for the instance and determines the maximum number of parallel servers if
PARALLEL_MAX_SERVERS parameter is not set. By setting this value, the parallel execution option is not enabled.
Parallel execution can be enabled by defining the PARALLEL clause at the table level or by adding a PARALLEL hint to
the SQL statement.
This parameter is OS dependent, and the default value of 2 is adequate in most cases. On systems that are I/O
bound, increasing this value could help improve performance.
PARALLEL_EXECUTION_MESSAGE_SIZE
As discussed earlier, the parallel servers are started and managed by the QC. During this process, all communication
between these processes happens by passing messages via memory buffers. In a RAC environment, these messages
are passed across the private interconnect. The amount of messages that can be sent is determined by this parameter.
On systems where parallel processing is significantly high, large amounts of such messages are sent. These message
transfers could cause latency to the overall response time of the query. It's important to size this parameter
appropriately to reduce parallel message latency.