SQL/MP Query Guide

Analyzing Query Performance
HP NonStop SQL/MP Query Guide—524488-003
6-15
Interpreting an EXPLAIN Plan
Selectivity
The EXPLAIN plan lists selectivities for tables and indexes. Selectivity values influence
the optimizer’s choice of the following:
•
Access path (base table, alternate index, or index only)
For example, if the restriction specified by a WHERE predicate does not result in a
low enough selectivity to justify alternate-index access, base-table access is
chosen instead. In such a case, the index plus base table access could be worse
than a base table scan with sequential prefetch.
•
Join order
The selectivity of each table determines the optimizer’s choice of the outer and
inner table.
•
Type of sorts performed
Check for table or index selectivities of 100 percent, which indicate costly table scans.
For more information about selectivity, see Section 5, Selectivity and Cost Estimates.
Locking Strategy
The EXPLAIN plan indicates the following:
•
Granularity of lock (row, partition, table).
•
Access option (browse, stable, repeatable).
•
Whether the lock mode (exclusive or shared) is chosen by the system or by the
user.
°
If the lock mode is chosen by the system, the plan indicates “chosen by the
system,” but does not show which mode was chosen.
°
If the lock mode is specified by the user, the plan simply states “Share” or
“Exclusive.”
Check for unexpected lock escalation and check the access option. Is the system
choosing stable access (the default) when browse access is sufficient? Browse access
enables you to read data currently being updated or deleted. If potentially inconsistent
data is unacceptable, do not specify browse access.
Parallel Execution
EXPLAIN indicates at the beginning of the plan whether parallel execution is used and
for which operations.
Parallel execution is especially useful when a large number of rows needs to be
processed by the executor, but only a small number of rows needs to be returned to
satisfy the query. (Section 4, Improving Query Performance With Environmental