SQL/MX 2.x Query Guide (G06.24+, H06.03+)

Parallelism
HP NonStop SQL/MX Query Guide—523728-003
8-13
How to Determine if You Have a Parallel Plan
Partitioned parallelism in query plans is represented by the operators of the Exchange
group: ESP_EXCHANGE and SPLIT_TOP. If your query plan does not contain either
of these operators, parallel processing was not used in the plan.
An additional exchange operator, PARTITION_ACCESS, appears in parallel plans but
signifies neither ESP nor DAM parallelism. The PARTITION_ACCESS operator is used
to describe a portion of an execution plan in which there are requests to DAM without
partitioned parallelism. One DAM process is requested at a time.
For information about exchange operators, see Section 7, Operators and Operator
Groups.
How the Optimizer Chooses the Number of ESPs
The ESP_EXCHANGE operator represents ESP parallelism in a query plan. The
number of ESPs chosen for the parallel plan depends on several factors:
•
Total number of CPUs in the cluster, which is computed from:
°
The number of CPUs per node in the cluster
°
The number of nodes in the cluster
•
The maximum number of ESPs for an operator is the number of CPUs times the
default setting value MAX_ESPS_PER_CPU_PER_OP. The default value is 1.
You cannot have more ESPs than CPUs unless you set the default
MAX_ESPS_PER_CPU_PER_OP. You might want to increase the default
MAX_ESPS_PER_CPU_PER_OP if your query is I/O bound or if you detect that
CPUs are not being completely utilized. For example, a plan that runs on a node
(16 CPUs) and accesses 64 partitions would normally use 16 ESPs: one for each
CPU. Changing the default value of MAX_ESPS_PER_CPU_PER_OP to 2 might
result in 32 ESPs. For more information, see System Default Settings That Affect
Parallelism on page 8-25.
Example
This example shows ESP parallelism in the query tree for the query:
SELECT * FROM ORDERS O, LINEITEM L
WHERE O.O_ORDERKEY=L.L_ORDERKEY
AND O.O_TOTALPRICE < 25000 AND L.L_QUANTITY < 5;
This query looks for small order information where the total price is less than $25,000,
and the quantity ordered is less than 5 units. Figure 8-1 on page 8-14 shows the query
tree. The EXPLAIN output shows where ESP processes are used for parallelism (the
tokens related to parallelism are highlighted). The tables are partititioned on
O_ORDERKEY and L_ORDERKEY.