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

Parallelism
HP NonStop SQL/MX Query Guide—523728-003
8-25
System Default Settings That Affect Parallelism
•
Complexity and quantity of rows returned. The optimizer chooses parallel plans
when complex processing on large amounts of data must occur, resulting in a few
rows, such as computing an aggregate.
•
Forcing parallelism. Use the CONTROL QUERY SHAPE statement to force
specific types of parallel plans. See Section 5, Forcing Execution Plans.
•
Defaults. Certain defaults might affect the parallel plan, as noted next.
System Default Settings That Affect Parallelism
For more information about default settings and how to override the system-defined
default setting, see the SQL/MX Reference Manual.
•
ATTEMPT_ESP_PARALLELISM
If ON, the optimizer tries to use the maximum degree of ESP parallelism whenever
possible. If the table is too small, the optimizer might not consider ESP parallelism
for some operators even with the ON setting. If OFF, the optimizer never generates
and costs plans that use ESP parallelism. If SYSTEM (the system-defined default
setting), the optimizer considers on an operator-by-operator basis whether it would
be worthwhile to generate and cost plans that use ESP parallelism. If the optimizer
chooses to do so, the SYSTEM setting also lets the optimizer choose the level of
parallelism to use. For each operator, the optimizer uses various heuristics based
on the logical and physical operator properties, such as the row selectivity,
memory usage, and so on, to determine if generating a parallel plan is worthwhile.
•
ATTEMPT_ASYNCHRONOUS_ACCESS
Enables/disables nowaited access for accessing partitions at the same time (DAM
parallelism). The system-defined default setting is ON. This option can greatly
improve the performance of SQL/MX queries; it has a lower cost than parallel
execution using ESPs. It is recommended that you keep this setting enabled.
•
MAX_ESPS_PER_CPU_PER_OP
Maximum number of ESPs the optimizer considers starting for each CPU for a
given operator. The system-defined default setting is one. If set to a value greater
than one, the optimizer considers plans where more than one ESP per CPU per
operator is considered. For example, if you have four CPUs available and
MAX_ESPS_PER_CPU_PER_OP is set to two, the optimizer considers plans
where an operator uses eight ESPs (if parallel execution is enabled). This strategy
might be advantageous if the CPU load for the query is light but the I/O load is
heavy. For example, if the query is I/O bound, and the CPU bandwidth is not
needed for other purposes.