SQL/MP Query Guide

Analyzing Query Performance
HP NonStop SQL/MP Query Guide—524488-003
6-12
Interpreting an EXPLAIN Plan
Interpreting an EXPLAIN Plan
When you examine the EXPLAIN plan, look at all factors and information about the
query. Use this information to assist you in tailoring queries so that they are efficient
and return the information you require.
You should examine the EXPLAIN plan for the following:
•
Total cost
•
Table scans
•
Access path
•
Sort operations
•
Correlated subqueries
•
Key predicates and MDAM predicate sets
•
Executor predicates
•
Sequential block buffering
•
Sequential cache
•
Selectivity
•
Locking strategy
•
Parallel execution
Total Cost
EXPLAIN assigns a total cost to scans and sorts, which represents the cost of doing all
the operations to complete the statement.
Cost is an estimate and not an exact measure. Many variables that the optimizer
cannot control can affect the actual run time execution. Cost, however, can be useful
for comparing the different execution plans for a given query.
Note that the total cost is not the sum of the costs of each step. For example, nested
join steps usually have costs that are multiplied instead of added. Also, sort costs are
not linear with the number of input rows.
For OLTP requests, watch for costs that are high. In some production application
programs, costs in the millions are possible. The maximum displayable value is equal
to the size of an SQL LARGEINT value—a maximum value of (2**63) − 1. Any amount
greater than this cannot be displayed. In some cases, reformulating queries with very
high costs can reduce execution time considerably. For fast OLTP response (seconds),
the total cost should be low.
For more information about cost, see Evaluating Cost Estimates on page 5-19.
The following subsections describe factors that influence cost.