SQL/MP Query Guide

The Optimizer
HP NonStop SQL/MP Query Guide—524488-003
2-5
Processor Assignment by the SQL/MP Optimizer
and Executor for Executor Server Processes (ESPs)
Processor Assignment by the SQL/MP
Optimizer and Executor for Executor Server
Processes (ESPs)
Apply these rules:
1. The SQL/MP optimizer tries to assign the ESP the same processor as that
assigned for the primary disk process of the table partition that the ESP will
access.
2. If the disk processor has already been assigned to another ESP, postpone the
assignment until other ESPs are assigned to the processors.
3. For all the unassigned ESPs, use the round-robin approach to distribute the rest of
the processors:
a. For the system in which the partition resides, use a processor that is not yet
assigned in the first round and assign it to the unassigned ESP.
b. If there are no free processors, the optimizer marks it as a case where there
are more partitions than processors. It then tries to assign processors by
repeating Step 1 through Step 3. This strategy involves reusing all the
processors in the system (that is treating all processors as not-in-use) to
increase the likelihood that the second round will succeed in assigning a
processor to a partition.
c. If no processors are available to the system (that is, if it is a remote partition
access or an error occurs in accessing disk information), the current processor
status is set to -1, indicating that the SQL/MP executor will assign the
processor at run time.
4. During run time, the SQL/MP executor runs a check to see if the processor
mentioned in the plan is available. If the planned processor is not available or if the
status is set to -1, any processor on the system is assigned.
5. For a parallel repartitioning plan, the temporary tables and ESPs that access them
are distributed evenly across available processors. Each ESP is assigned to the
processor that is the primary disk process processor for the temporary table that a
specific ESP will access. If the amount of data to be repartitioned is large, more
than one temporary table (and related ESPs) might be assigned to the same
processor.