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

When the parameter is set to AUTO , this means that the auto DOP is active and the optimizer automatically
decides if the statement should execute in parallel or not and what DOP should be used for the operation. While
generating the DOP for the statement, the optimizer will take into consideration the resource requirements for
the statement and if the estimated elapsed time for the statement is less than PARALLEL_MIN_TIME_THRESHOLD , the
statement will run in a serial mode.
On the other hand, if the estimated elapsed time is higher than PARALLEL_MIN_TIME_THRESHOLD , the optimizer
uses the costs to complete the operation and computes the DOP required to complete the operation. Once again, to
avoid all the resources getting consumed by one operation, the optimizer further places a control based on parameter
PARALLEL_DEGREE_LIMIT . The PARALLEL_DEGREE_LIMIT parameter drives the optimizer to take into consideration the
number of CPUs available on the server or the capacity of the I/O subsystem.
Valid values for this parameter are
AUTO : Enables auto DOP, statement queuing, and in-memory parallel execution.
MANUAL : Disables auto DOP, statement queuing, and in-memory parallel execution. The
optimizer reverts the behavior of parallel execution to what was available under older
versions of Oracle.
LIMITED : Enables auto DOP; however, statement queuing and in-memory parallel
execution features are disabled. Auto DOP is only applied to statements that access tables
or indexes that have explicit PARALLEL attribute values.
PARALLEL_MIN_TIME_THRESHOLD
This parameter determines the minimum execution time a statement should have before the default auto DOP is
applied to the statement. The default value for this parameter is 10 seconds and is derived from the underscore
parameter _parallel_time_unit . 7 The optimizer first calculates a serial execution plan for the SQL statement, if the
estimated execution elapse time is greater than PARALLEL_MIN_TIME_THRESHOLD ; the statement becomes a candidate
for automatic DOP discussed earlier.
PARALLEL_DEGREE_LIMIT
Not all systems have sufficient resources to support parallelism; in other words, resources are limited and what is
available is shared between all that need them. Because of this, the optimizer automatically decides if parallelism is
even an option and what DOP the statement should use. The limitations are enforced by the optimizer through the
parameter PARALLEL_DEGREE_LIMIT .
The driving factor used when determining such limitations is either based on I/O or CPU. This is a new
parameter introduced in Oracle Database 11g Release 2. This parameter drives if the parallel plan generated by the
Oracle's cost-based optimizer should be driven by the I/O resources or the CPU resources available to the session, and
thus the values for this parameter are I/O or CPU.
I/O
This value tells the optimizer that the maximum DOP is limited by the I/O capacity of the system. The values are
calculated by dividing the total system throughput by the maximum I/O bandwidth per process. For the optimizer to
obtain realistic I/O capacity, it is advised that the I/O system is calibrated using the Oracle provided package,
DBMS_RESOURCE_MANAGER.CALIBRATE_IO .
7 Underscore parameters are hidden parameters and should be modified only with prior guidance from Oracle support.