HP Neoview Release 2.5 Software - Manual (Page 98)

Analyze the Explain Plan Through the Grid
You can sort on the fields in the Explain plan grid (middle section). Note that when you click on
an operator in the query plan, the row is highlighted in the grid section. You can sort on the
fields in the grid, so if you are interested in the total cost of an operator, you can sort them in
ascending or descending cost. While you may find it useful to analyze information through the
grid, keep in mind that after your analysis, you need to re-explain the plan again to align the
operator tree view and the grid contents. You can sort the query plan through the grid level or
by the tree view by using the Tools>Options>Query Workbench Settings. When enabled,
sorting is done through the grid level. When disabled, sorting is done through the operator tree
view. For detailed information about the query operators, see the Neoview Query Guide.
About the ESP_EXCHANGE and HYBRID_HASH_JOIN Operators
In the Explain output, NPA provides additional information about the ESP_EXCHANGE and
HYBRID_HASH_JOIN operators. The abbreviations describe the type of operation performed.
ESP_EXCHANGE can show these abbreviations:
• BR for Broadcast partition
• HP for Hash partition
• RN for Hash partition with Random numbering
HYBRID_HASH_JOIN can show these abbreviations:
• MP for Matching Partition
• NMP for Nonmatching Partition
Process Boundaries
Process boundaries are differentiated in query plans by color. Process boundaries are captured
in the query plan in the fragment_type and fragment_id fields of the query plan. You can turn
off process boundary coloring by unchecking the Color Process Boundaries setting in the
Tools>Options>Query Workbench Settings.
The colors are defined as follows:
• Master process (or root): white
• ESP process: varying shades of blue (up to 40 shades)
• Disk process (ESAM): yellow
Process Architecture For a Query
Several processes are required to execute a query plan. You will find it helpful to understand
the processes where operators execute when you need to identify problems in queries.
• MXOSRVR is the database server through which NPA connects. This process is also called
the master executor or root and controls the overall execution of the query. Each NPA user
connection is made through a separate MXOSRVR process.
• MXCMP is the Neoview compiler and compiles the query and generates the query execution
plan. A separate MXCMP process is dedicated to each database server process.
• ESP (Executor Server Process) is a helper process used for parallel execution of a query. Each
query can have many ESPs with the maximum of 1 ESP per CPU per plan step. ESPs are
dedicated to an active server connection and then available for reuse. If the connection is
broken and another user connects to the server, the ESPs are available to the new user.
• Disk process Encapsulated SQL Access Manager (ESAM) is a disk process manager that
manages access to data for the volume. ESAM processes are shared among all active queries
and are never dedicated processes. One logical disk process is allocated per disk volume
(implemented as a set of processes per disk volume).
Each operator executes within a process, and some operators might execute in different processes.
For example, the join operators can execute in either the master executor or ESP process. Sorts
98 Use the Query Workbench