SQL/MP Query Guide

HP NonStop SQL/MP Query Guide—524488-003
6-1
6 Analyzing Query Performance
Different queries have varying levels of impact on your system. One way to estimate
query use is with the 90-10 rule, which estimates that 10 percent of the queries use up
90 percent of critical resources. The 90-10 rule can help you determine which queries
are most important from a performance viewpoint.
Note, however, that performance evaluation at the statement level should be done on a
system that is as well-tuned as possible. For more information on system tuning and
performance, see the SQL/MP Installation and Management Guide.
This section discusses these topics:
•
Guidelines for Tuning Queries on page 6-2
•
Preparing Your Queries on page 6-3
•
Using DISPLAY STATISTICS on page 6-3
•
Using Measure on page 6-7
•
Using EXPLAIN on page 6-8
•
EXPLAIN Plan for Simple SELECT on page 6-16
•
EXPLAIN Plan for Primary Access on page 6-18
•
EXPLAIN Plan for Index-Only Access on page 6-19
•
EXPLAIN Plans for Bounded Predicates on page 6-20
•
EXPLAIN Plan for Key Predicates on page 6-23
•
EXPLAIN Plan for DISTINCT on page 6-24
•
EXPLAIN Plan for ORDER BY on page 6-25
•
EXPLAIN Plans for GROUP BY on page 6-26
•
EXPLAIN Plans for Subqueries on page 6-29
•
EXPLAIN Plans for CASE on page 6-33
•
EXPLAIN Plans for String Functions on page 6-39
•
EXPLAIN Plans for MDAM on page 6-42
•
EXPLAIN Plan for Determining the Cost of Multiple Predicate Sets on page 6-46
•
EXPLAIN Plan for Selectivity for Range Predicates on page 6-47
•
EXPLAIN Plans for Join Queries on page 6-48
•
EXPLAIN Plan for UNION Operation on page 6-68
•
EXPLAIN Plan for MAX Optimization on page 6-70
•
EXPLAIN Plan for Cursor UPDATE on page 6-72