Neoview Query Guide (R2.2)

In this nested join plan, 128 ESPs are started to do the join in parallel. In this join, the inner and
outer tables do not match. The ESPs still perform the joins independently of one another. However,
some contention might exist on the inner table because all ESPs can access any of the inner table
partitions at the same time, unlike the hybrid hash join plan shown previously. This potential
contention is shown by the SPLIT_TOP operator, which is used with the PARTITION_ACCESS
operator for the inner table. The outer table does not use a SPLIT_TOP operator. Because of this,
each ESP accesses exactly one outer table partition. No I/O contention exists to the outer table
partitions from the ESPs.
DAM and ESP Parallelism
Partitioned parallelism uses different processes, depending on the type of operators being
processed: DAM processes and ESPs.
• DAM parallelism indicates parallel execution in multiple DAM fragment instances. The
instances might be accessing different tables, such as in a join or union query, or they might
be accessing different partitions of one table under coordination of a SPLIT_TOP operator.
DAM parallelism is characterized by no-wait communication (asynchronous access). This
form of parallelism is inexpensive because it uses existing disk processes but it is limited in
use. For example, DAM processes cannot repartition, and they might need to service other
requests.
• ESP parallelism refers to any parallel plan with at least one ESP plan fragment. ESP
parallelism occurs when a plan fragment executes within an executor server process (ESP).
A cost is associated with starting an ESP process. The optimizer balances this cost against
the performance gain resulting from the increased parallelism and chooses ESP parallelism
only if the gain exceeds the ESP start-up cost.
The PARTITION_ACCESS operator 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.
30 Using Visual Query Planner