SQL/MP Query Guide

Analyzing Query Performance
HP NonStop SQL/MP Query Guide—524488-003
6-14
Interpreting an EXPLAIN Plan
If the EXPLAIN plan indicates there are no key predicates, you might want to review
the query and consider adding search conditions, based on leftmost key columns, that
restrict the number of rows accessed, if feasible. For more information, see Writing
Efficient Predicates on page 3-15.
A lack of key predicates can also be a factor in causing a full table scan.
When the optimizer uses MDAM, the EXPLAIN plan shows the result of converting the
key-column predicates to MDAM predicate sets. These predicate sets show how the
optimizer processes the predicates.
Executor Predicates
Check the EXPLAIN plan for evaluation of predicates at the executor level.
The most efficient predicate evaluation is at the disk process level. Executor evaluation
indicates a bigger impact on performance. For more information on which predicates
cause evaluation at the executor level, see How the Optimizer Processes Predicates
on page 3-4.
Sequential Block Buffering (SBB)
If you specify sequential access by using the CONTROL TABLE SEQUENTIAL
directive, the optimizer uses virtual sequential block buffering (VSBB) and prefetch
techniques, if feasible.
In some cases, the preferred SBB might not be selected if the criteria required for SBB
are not met or if you have disabled sequential buffering (by using the CONTROL
TABLE directive). For more information, see Reducing Messages With Buffering
Options on page 4-21.
When you expect SQL to return only a few rows, you may want to turn off SBB to
eliminate unnecessary processing of additional rows. When opening a cursor, fetching
one row, and closing the cursor, use a CONTROL TABLE SEQUENTIAL READS OFF
statement. This statement will keep SQL from processing additional rows to fill a VSBB
buffer.
Sequential Cache
In the description of the access path, the EXPLAIN plan states whether access is
sequential and whether data is kept in cache memory, or sequential cache, for only a
short time.
With sequential cache, as soon as all the rows in a block have been used, the block is
discarded twice as quickly as it would have been if sequential cache were not used.
Sequential cache is ignored if the threshold for sequential prefetch is not reached.
For more information about the cache buffer, see the SQL/MP Installation and
Management Guide.