Database Reference
In-Depth Information
In Figure
12-4
, two instances are participating in a partition-wise join between SALES and ORDERS tables.
PX servers P001 and P002 participate in this join operation in both instances. P001 reads partition P1 of the SALES
table and partition P1 of the ORDERS table and joins these two partitions. Similarly, process P002 reads partition P2
of the SALES table and partition P2 of ORDERS table and joins the rows from these two partitions. PX messages are
kept minimal between PX server processes, since this type of execution does not use the Consumer/Producer model.
Finally, a result set is sent to the QC, reducing interconnect traffic for an inter-instance parallel operation.
QC
P001
P001
P002
P002
P1
P3
Sales
P2
P4
P1
P2
P3
P4
Orders
Instance 1
Instance 2
Figure 12-4.
Partition-wise join
However, specific conditions must be met for partition-wise join to be chosen. Some requirements for partition-wise
joining include matching of parallelism chosen for joining tables and matching of the partition boundaries of the
partitioned tables.
Summary
Parallel execution is an effective feature in a RAC environment. Although Oracle Database is optimized for effective
placement of PX servers to reduce interconnect traffic, you should design interconnect bandwidth to match your
application workload. If your application workload is mostly data warehouse queries, then you could expect higher
interconnect traffic due to parallel execution. The following are the essential key elements of optimal practices in
setting up parallelism in RAC:
1.
Use services to control placement of PX servers. Placement using the instance_groups
parameter is deprecated, but placement using a combination of services and
parallel_instance_group is allowed.
2.
Verify that the PEMS parameter is set to 16K. In PX-intensive environments, use Jumbo
Frames.
3.
If parallel queries do not get enough PX servers, use the Parallel Statement Queuing
feature to provide consistent elapsed times.
4.
For DDL statements on huge tables, if one node can handle the PX execution, then
set up parallel parameters so that PX servers can be allocated from one node or use a
parallel_force_local parameter to force PX server allocation to a node.
5.
Use raccheck and exacheck tools to verify parallelism setup in a RAC environment.
