SQL/MP Installation and Management Guide

Enhancing Performance
HP NonStop SQL/MP Installation and Management Guide—523353-004
14-7
Keeping Statistics Current
Keeping Statistics Current
SQL/MP provides an UPDATE STATISTICS utility to collect and save statistics on
columns and tables. The SQL compiler uses these statistics to help determine the cost
of access plans. When you have current statistics for a table, you increase the
likelihood that the optimizer chooses an efficient plan.
The UPDATE STATISTICS utility changes the information about a table and its indexes
in the catalog so that the information more accurately represents the current content
and structure of the table. This information is used by the SQL compiler to determine
an access strategy.
The UPDATE STATISTICS statement must be user initiated. SQL/MP does not
automatically update statistics during DDL operations or following utility commands
such as LOAD.
The statistics that UPDATE STATISTICS collects are:
•
Current number of rows in the table
•
Byte address of EOF
•
Percent of nonempty blocks
•
Number of index levels for each index on the table
•
Number of unique entries in each column
•
Second highest value in each column
•
Second lowest value in each column
Knowing When to Update Statistics
You might want to run UPDATE STATISTICS after loading or re-creating a table, after
structural changes such as creation of an index, or after significant update activity
(growth in database size). Before running UPDATE STATISTICS, however, you should
consider these:
•
If you experience performance degradation, check for fragmentation of blocks. Use
the FILEINFO command with the STATISTICS option set on. If blocks are
fragmented, running UPDATE STATISTICS and recompiling the queries will not
help; first reload the table online by using the FUP RELOAD command.
Note that, if an object has the UNRECLAIMED FREESPACE or INCOMPLETE
SQLDDL OPERATION attribute set, the FILEINFO STAT results might include
extraneous records. For more information, see Altering Partition Attributes on
page 7-19.
•
Run UPDATE STATISTICS only after a table has been loaded with data. Do not
run UPDATE STATISTICS when a table is empty.