SQL/MX 3.2.1 Query Guide (H06.26+, J06.15+)
Parallelism
HP NonStop SQL/MX Release 3.2.1 Query Guide—663851-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, SQL/MX Operators.
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.
TP663851.fm Page 13 Wednesday, January 30, 2013 5:37 PM