Neoview Query Guide (R2.3, R2.4, R2.5)

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.
ROOT Operator
The ROOT operator is the root, or the top operator, of an execution plan and describes the SQL
query. This operator and all operators subordinate to it execute inside the master process until
another process boundary (ESP process or DAM process) is reached. The ROOT operator appears
in every query execution plan.
The ROOT operator has one child. The description field for this operator contains:
Data TypeToken DescriptionToken
integerA sequential number assigned to the fragment. 0 is always master
executor and 1 is reserved for the Explain plan. Numbers 2 to n will be
ESP or DAM fragments.
fragment_id
integerThe fragment_id for the parent fragment of the current fragment. Value
is (none) for master executor.
parent_frag
textmaster, ESP, or DAMfragment_type
textIndicates whether an optimization for short, simple operations is used.
The value used is returned if this optimization is used. Blank if not
used.
olt_optimization
integerStatement index of this statement.statement_index
textDetermines the type of statement atomicity chosen for a query:
XN_ROLLBACK: Transaction is rolled back if an error occurs.
NO_XN_ROLLBACK: Upon error, query stops executing and error is
returned. Transaction is NOT rolled back. Only appears if you request
a WITH NO ROLLBACK type of transaction. RETURN: Query stops
executing and error is returned without need for statement rollback.
SAVEPOINT: DAM savepoints are used to roll back the statement if an
error occurs. PARTIAL_UPD: Partial results are updated and an error
is returned.
upd_action_on_error
textAppears only if text is READ ONLY. READ ONLY transactions are
obtained for select statements when autocommit is ON, or if you have
requested it through a SET TRANSACTION statement.
xn_access_mode
20 Query Plan Operators