Database Reference
In-Depth Information
Note that it is still possible for parallel execution to suffer from cache fusion waits for undo, undo header, and
segment header blocks. For example, if a block has an uncommitted transaction, then PX server processes will access
undo blocks/undo header blocks to construct a Consistent Read (CR) version of the block, which would require global
cache locks.
PEMS
In an inter-instance PX execution, PX servers transmit buffers through the private interconnect and the size of
transmission buffer is controlled by the parameter
parallel_execution_message_size
(PEMS). Prior to 11g,
the default value of this parameter was about 2KB. With smaller PEMS, data transmission between the PX server
processes is inefficient due to chattier transmission.
From Oracle Database version 11.2 onward, if the compatibility parameter is set to 11.2.0 or higher, then PEMS
defaults to a value of 16K. If your application uses the PX execution feature heavily, then you must verify that PEMS is
set to a value of at least 16K.
In PX-intensive environments, it is also advisable to use Jumbo Frames so that chatty network traffic can be
reduced. In my experience, the combination of Jumbo Frames and PEMS set to 16K increased throughput of PX
workload in a production database dramatically.
Parallelism Features and RAC
Oracle Database version 11.2 introduced three important parallelism features, and version 12c enhances these
features. The feature
In-memory parallelism
introduces buffered reads for PX workload. The
AutoDOP
(automatic
DOP) feature introduces automatic PX plan, and with the
PX Statement Queuing
feature, PX is delayed until
sufficient PX server processes are available.
All three features are controlled by a master switch parameter, namely,
parallel_degree_policy
.
1.
If you set the parallel_degree_policy parameter to a value of AUTO, then all three features
are enabled.
2.
If you set the parallel_degree_policy parameter to a value of LIMITED, then that parameter
value disables PX statement queuing and in-memory parallelism. However, for statements
accessing objects decorated with a parallel degree of DEFAULT, the AutoDOP feature is
enabled.
3.
If you set the parallel_degree_policy parameter to a value of MANUAL, then these three
new features are disabled.
4.
Version 12c introduces AUTO_FEEDBACK, a new value which is a combination of AUTO
and FEEDBACK. All three parallel features discussed earlier are enabled with the AUTO
value. In addition, a FEEDBACK mechanism to the cursor in memory is used to mark a
statement to be reparsed with a different DOP. Two types of feedback mechanisms are
possible: statement-level time feedback and operator-level time feedback. After a parallel
statement execution, if the calculated DOP is significantly
3
different from actual DOP,
then the statement is signaled to be reparsed with actual DOP during the execution. This
feature is more granular than just the statement-level feedback and tracks the DOP at the
operation level. If the actual operator-level DOP is different from estimated DOP, then the
operator-level execution DOP is used as a feedback mechanism while reparsing the SQL
statements during subsequent execution of the cursor.
I will introduce these features and discuss how they can be effectively utilized in a RAC environment.
3
Significantly different means that actual DOP is lower than 0.5 * estimated DOP or higher than 2 * estimated DOP.
