SQL/MP Installation and Management Guide

Enhancing Performance
HP NonStop SQL/MP Installation and Management Guide—523353-004
14-9
Testing UPDATE STATISTICS
table does not have a very large number of columns, you should probably use the
ALL option whenever you update the statistics.
•
If you use the NORECOMPILE option of UPDATE STATISTICS, the operation
does not invalidate the dependent programs. If you want to take advantage of the
new statistics, however, you must explicitly SQL compile the dependent programs.
•
If you use the RECOMPILE option (the default), the UPDATE STATISTICS
operation invalidates dependent programs so that the programs are automatically
recompiled when subsequently used. You should explicitly SQL compile these
programs to avoid automatic recompilation.
•
When statistics are being updated for a table, T, any DDL or DML operation on T
might get a timeout error (SQL error -4066) because T is already opened for
exclusive access. During the UPDATE STATISTICS operation, the entry for T in
the TABLES catalog table is locked. The catalog is available for other SQL
operations; however, if other operations attempt to access the record for T in
TABLES, then SQL error -8300 is returned, indicating that the record is locked.
UPDATE STATISTICS writes the new statistical information into the catalog tables.
After the statement is performed, you cannot undo it. Subsequent compiles, either
explicit or automatic, create a best available query execution plan based on the new
statistical information.
Testing UPDATE STATISTICS
Because of the significant effect of running UPDATE STATISTICS, you can try to
determine the benefits of the operation before you commit to updating the catalog
tables. These steps provide two methods for testing the results of UPDATE
STATISTICS: one method is for a test environment, the other for a production
environment.
To test UPDATE STATISTICS in a test environment, follow these steps:
1. Test a sample set of queries against the production tables by using DISPLAY
STATISTICS to obtain the statistical information.
2. Duplicate the table or tables involved to a test location. For a large database,
duplicate a subset of the table or tables involved to a test location.
3. Enter an UPDATE STATISTICS statement.
4. Test the same queries against the tables using DISPLAY STATISTICS to obtain the
new statistical information.
5. Determine any improvement in performance.
6. If performance improves, enter the UPDATE STATISTICS on the production
database.
To test UPDATE STATISTICS in a production environment, follow these steps: