SQL/MP Installation and Management Guide

Enhancing Performance
HP NonStop SQL/MP Installation and Management Guide—523353-004
14-8
Analyzing the Possible Impact of Running UPDATE
STATISTICS
•
Run UPDATE STATISTICS after creating a new index for a table; otherwise, SQL
returns a warning for subsequent operations on the table.
•
Do not run UPDATE STATISTICS when the UNRECLAIMED FREESPACE or
INCOMPLETE SQLDDL OPERATION attribute is set. The results might be
incorrect. For more information, see Altering Partition Attributes on page 7-19.
Other performance issues to consider when you experience reduced response time
are:
•
The volume containing the table might have heavy disk usage.
•
If the table or index is distributed, the network might be rerouted or might have
heavy usage.
•
The programs might be automatically recompiling, thereby decreasing
performance.
•
Unusually long ad hoc queries or reports might be reducing response time.
•
Programs might be waiting for locked data.
Analyzing the Possible Impact of Running UPDATE STATISTICS
Depending on the size of the table, updating statistics can take longer than you would
like; therefore, run UPDATE STATISTICS during off hours when peak performance is
not required. You can determine the effect of UPDATE STATISTICS on a production
query by bracketing UPDATE STATISTICS and EXPLAIN on the queries in a
transaction.
If you want to preserve the existing query execution plan, you must be aware that
running UPDATE STATISTICS might cause the optimizer to choose a different plan.
UPDATE STATISTICS could, for example, change the access path choices made for
queries and programs. Usually, you can improve performance by updating the statistics
on a table to reflect the current status. The UPDATE STATISTICS operations, however,
might not improve performance, as discussed in these paragraphs:
•
UPDATE STATISTICS performs a sampling of rows to determine the statistical
information. For very large tables, this procedure can take perhaps 10 to 15
minutes per partition. Because this is a statistical sampling method, the statistics
gathered are not exact. Any sampling error, however, should not affect the overall
performance of the access method.
•
The ALL option of the UPDATE STATISTICS statement specifies that you want
statistics updated for all columns. If you do not specify this option, only the
columns that make up the primary key and columns that have been specified in
any index are updated.
•
The UPDATE ALL STATISTICS statement might require additional time to gather
information on large tables with many columns. This statement, however, ensures
a complete analysis of columns that might be used in queries or indexes. If the