SQL/MP Installation and Management Guide

Enhancing Performance
HP NonStop SQL/MP Installation and Management Guide—523353-004
14-11
Running UPDATE STATISTICS
>> DISPLAY STATISTICS;
>> ROLLBACK WORK; <--Rolls back the TMF transaction
You should only test UPDATE STATISTICS in a lightly loaded system; otherwise, the
performance will be influenced by the general system load, which could mask
differences in query results.
Running UPDATE STATISTICS
Always specify the NO RECOMPILE option when using UPDATE STATISTICS, for
these reasons:
•
By default, an UPDATE STATISTICS operation invalidates dependent programs,
even if UPDATE STATISTICS is executed within a transaction that is backed out.
Catalogs are audited; program file labels are not. Because program file labels are
not audited, updates to program file labels are not backed out. Consequently, if a
transaction is backed out, the program file labels are left in an invalid state while
the catalog specifies a valid state.
•
To avoid invalidating dependent programs and therefore avoid inconsistencies
between the program file label and the catalog. Until you explicitly compile the
affected programs, however, they will not use the new statistics.
For a thorough evaluation of access options, include key columns, index columns, and
those nonindex columns that participate in predicates. To update statistics for all
columns, you must specify UPDATE ALL STATISTICS.
This example updates statistics for primary key columns of the EMPLOYEE table and
columns that have been specified in any alternate index on the table:
UPDATE STATISTICS FOR TABLE EMPLOYEE NO RECOMPILE;
This example requests statistics by reading all rows in the first 50 blocks of each
partition of the EMPLOYEE file:
UPDATE STATISTICS FOR TABLE EMPLOYEE SAMPLE 50 BLOCKS;
You can choose to read the entire table (EXACT option) or a specified number of
blocks of each partition (SAMPLE n BLOCKS option) for computing statistics. These
options help control the amount of time spent calculating statistics. If neither of these
options is specified, statistics are collected by reading all rows in partitions smaller than
1,000 blocks and approximately 500 blocks from each partition larger than 1,000
blocks.
Note. There is one problem with the preceding scenario: you should not enter the UPDATE
STATISTICS statement within a user-defined TMF transaction. If the table is large, the user-
defined transaction might cause an error on the TMF audit trails. Normally, UPDATE
STATISTICS starts the appropriate number of TMF transactions but does not include the
scanning of the table for information within a TMF transaction. If you want to use this sample
procedure, make sure the TMF audit trails can handle the workload.